delete 1 record from a table and automatically delete all records associated with the user_id in mysql

I have been trying to learn from reading tutorials online and stuff but I just can't put my finger on it.

I have 2 tables at the moment (i'll have a lot more later on as I build my application) so I want to knock out this issue before expanding and coding.

I have my tables set to use InnoDB and I have each table related to each other by using user_id as foreign keys.

If i issue a DELETE query on the main users table, how can i get all records from other tables that are linked to the user_id field get deleted as well?

I know its simple, but I think I just need to ask the question myself so I can 开发者_如何学编程understand the answer rather than reading the answer... heh

thank a lot for any help.

Since they are InnoDB tables with proper FK relationships, you can simply use ON DELETE CASCADE in the foreign key definition. For example in one of the related tables:

FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE

However, this requires altering your existing schema to modify the foreign keys.

See the MySQL FOREIGN KEY docs for complete information.

You can cascade the DELETE using a DELETE trigger or using the technique Michael suggested, or you can manually write enough DELETE statements (deleting the entities from the bottom of the hierarchy upwards so you don't violate the foreign key constraints) though the latter is clearly not an ideal solution in terms of maintenance.

You may wish to use the TRIGGER approach if you want to fine tune the delete process (e.g. in case you don't want to destroy certain data related to the foreign key, or if you wanted to move it elsewhere or associate it to a different ID).

this is mysql example

 ALTER TABLE  table_with_foregin_key ADD FOREIGN KEY (  foreign_key_column )
 REFERENCES  table_name (
 user_id
 ) ON DELETE CASCADE ON UPDATE CASCADE;