MySQL, foreign key constraint does not make any difference

Question

I have a database, with several tables. The "main" table, called contacts, stores information about contacts, each having an id, a name etc...

I also have like, 20 other tables, each representing a certain function, like staff, media ...

Each of these function-tables, references the contact table, with a foreign key. I've recently added this constraint to all the tables, with the following query, here for the table staff:

ALTER TABLE staff ADD contactsConstraint FOREIGN KEY (c_id) REFERENCES contacts(id) ON UPDATE CASCADE ON DELETE CASCADE.

If I change a row in contacts, I also want the change to appear in the staff table - and the other way around. But the constraint does not seem to make a difference! The only thing it does, is to throw an error if I try to add a row to staff, where the c_id value is not present in the contacts table.

What have I done wrong? It should be noted, that I recently changed the database-engine to InnoDB, to be sure that my constraints would work.

I have also tried to remove the ON DELETE CASCADE, so only updates to a table would take effect in both tables, but also with no effect.

Answer 1

You are confusing different concepts in your question. When your database is properly normalized, a change to data in one table should never have consequences in another table.[*]

If you want to change the primary key and have the referencing records in another table follow the change, you have to use ON UPDATE CASCADE.

ON UPDATE DELETE only deletes referenc*ing* records if you DELETE the referenc*ed* record. In your example, if you delete a record from contacts, all records in staff referencing the deleted record would be deleted too.

Please read the MySQL reference manual about foreign keys for more information.

[*] If you have the same field in multiple tables containing the same information, you have a denormalized schema, which may be good for performance, but requires you to make extra effort to (manually) update all copies of a field when updating it.