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
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
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
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.