I've got a database, with several tables. The "primary" table, known as contacts, stores details about contacts, each getting an id, a title etc...

I additionally have like, 20 other tables, each representing a particular function, like staff, media ...

All these function-tables, references the contact table, having a foreign key. I have lately added this constraint to any or all the tables, using the following query, for the table staff:

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

Basically change a row in contacts, I additionally want the modification to look within the staff table - and the other way round. However the constraint doesn't appear to really make a difference! The only real factor it will, would be to throw a mistake basically consider adding a row to staff, in which the c_id value isn't contained in the contacts table.

What have I done wrong? It ought to be noted, which i lately transformed the database-engine to InnoDB, to be certain that my constraints works.

I've also attempted to get rid of the ON DELETE CASCADE, so only updates to some table would work both in tables, but additionally without any effect.

You're confusing different concepts inside your question. Whenever your database is correctly stabilized, a big change to data in a single table should not have effects in another table.[*]

If you wish to alter the primary key and also have the referencing records in another table follow the modification, make use of ON UPDATE CASCADE.

ON UPDATE DELETE only removes referenc*ing* records should you DELETE the referenc*erectile dysfunction* record. Inside your example, should you remove an archive from contacts, all records in staff referencing the erased record could be erased too.

Please browse the MySQL reference manual about foreign keys to learn more.

[*] If you possess the same area in multiple tables that contains exactly the same information, you've got a denormalized schema, which might be great for performance, but requires you to definitely make extra effort to (by hand) update all copies of the area when upgrading it.