No foreign key constraints and need to do a complicated delete

I have a website which I have been working on creating very rapidly, and now am paying back some technical debt. I have a complicated issue:

My site deals with scheduling hikes. Once you create a hike, it has many things associated with it: a message board, list of attendees, the group it belongs to, the carpool, route, trailhead, etc.

Here is an example so you can see what I am talking about: http://www.comehike.com/hikes/scheduled_hike.php?hike_id=172

The technical debt I am talking about is that I never made foreign keys in the DB, and now need to do a cascade delete, and I am not sure how to go about it so that I don't introduce a 开发者_如何转开发million bugs :)

Should I make foreign keys for all the tables now? How should I do this?

Thanks, Alex

Check out the MySQL docs on FOREIGN KEY Constraints. Note that you'll need to be using innoDB tables.

ALTER TABLE <tablename>
ADD CONSTRAINT <fkname> FOREIGN KEY <index name>(<columns>)
REFERENCES <othertable> (<columns>)
ON DELETE CASCADE

I would suggest creating foreign keys for the tables in your DB. This will be a more robust way of dealing with the problem you are facing. You obviously understand what a foreign key imposes on the database, and how to deal with the keys.

If I was faced with this problem, I would use a graphical interface for the database if I had one (e.g. PhpMyAdmin), otherwise a quick google brings up some tutorials.

EDIT: From the linked tutorial, in a many-to-one relationship, you pace the key on the "many" table, indicating that a certain column in that table can only have values that are present in a certain column in the "one" table. Hi the link for a worked example.

When adding keys to a table that already has data, you may not be able to add the foreign key if some of the data is malformed. For example, if you have a phone number table referencing a person table (many phone numbers to one person) and you have any phone numbers with an invalid person_id (maybe person 5 was deleted and there is still a phone number with a person_id of 5) you will be unable to create the foreign key until you remove the offending phone number.

if you have not made formal foreign keys, the have you made the keys some other way that permits linking the tables or are all tables unrelated ?

If there is anyway to relate the tables then you will simply have to write a Cascading Delete code.

Otherwise its probabely a redesign or add in foreignkey fun. =))

If you have not already get yourself a copy of MySQL Workbench and redesign it from there adding in the foreign keys. This will generate the SQL code for you too.

I would go with creating foreign keys as well but if there is any reason that stops you from doing so there is another solution which is creating Triggers. You can tell triggers to basically do anything you want when an update,delete or insertion occurs to a table in the database including changing other tuples in other tables. Here are a couple of tutorials on how to create triggers:

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_7004.htm http://msdn.microsoft.com/en-us/library/ms189799.aspx

the first one seems more direct and clearer but if none of them helps just search google for DB triggers and you're all set!

I hope this helps :)