What effects by using a lot of foreign keys?
Namitha karupaiya
Ranch Hand
Posts: 89
Maximilian Xavier Stocker
Ranch Hand
Posts: 381
posted 11 years ago
Zero. MySQL doesn't support foreign keys so you can create a number if you want but they have no effect.
More of nothing is still nothing.
Unless you are using non-standard tables there is and never has been support for foreign keys in MySQL. It's one of the "features" that makes MySQL not an enterprise database system in my opinion.
Originally posted by Namitha karupaiya:
What is the maximum number of foreign keys allowed in a MySQL database table?
Zero. MySQL doesn't support foreign keys so you can create a number if you want but they have no effect.
What are the effects by using increased number of foreign keys?
Thanks.
More of nothing is still nothing.
Unless you are using non-standard tables there is and never has been support for foreign keys in MySQL. It's one of the "features" that makes MySQL not an enterprise database system in my opinion.
posted 11 years ago
Yes it does (as of v3.something). True its only for the InnoDB storage engine, anything else and MySQL very annoyingly checks the syntax of any foreign key definitions in a DDL but doesn't actually apply them!
[ April 12, 2006: Message edited by: Paul Sturrock ]
MySQL doesn't support foreign keys
Yes it does (as of v3.something). True its only for the InnoDB storage engine, anything else and MySQL very annoyingly checks the syntax of any foreign key definitions in a DDL but doesn't actually apply them!
[ April 12, 2006: Message edited by: Paul Sturrock ]
stu derby
Ranch Hand
Posts: 333
posted 11 years ago
I'm not aware of a limit; if there is a limit, it may be different for ISAM tables and InnoDB tables.
Properly used, each foreign key constraint imposes a performance penalty on many insert/update/delete operations; the more foreign keys on a table, the more penalty. Improperly used, even one foreign key can severly hurt insert/update/delete performance.
Consider two tables, MASTER and DETAIL; there is a one-to-many relation between MASTER and DETAIL, and every DETAIL record has to have a master. The MASTER table has a primary key named MASTER_ID, and a data column USER_NAME. The DETAIL table has a DETAIL_ID primary key, an OWNING_MASTER_ID column that has a foreign key constraint linking it to MASTER.MASTER_ID and is NOT NULL, and a DATE column for dates and an EVENT column for a description of what happened.
Every INSERT into DETAIL must validate the foreign key constraint and therefore check the that the value of OWNING_MASTER_ID actually exists in the MASTER table. Every UPDATE of the DETAIL table must also make sure that either no change is occuring to OWNING_MASTER_ID, or that the new value of OWNING_MASTER_ID exists in the MASTER table. And finally, every DELETE on the MASTER table must check that there are no records in the DETAIL table that have the MASTER record's MASTER_ID as the DETAIL record's OWNING_MASTER_ID.
That doesn't sound too bad, however the setup I have described above will perform well for INSERT and UPDATE, but badly for DELETE of MASTER records. The problem is that there is no index on OWNING_MASTER_ID; each and every record in DETAIL has to be checked every time one record is deleted from MASTER. The problem is resolved by adding a non-unique index on the OWNING_MASTER_ID column.
It's practically an absolute requirement with foreign key constraints on tables with more than a tiny number of rows that the columns at both ends of the constraint be indexed (a primary key column is implicitly indexed).
Maintining all those indexes takes up more space in the database and further slows down INSERT/UPDATE/DELETE operations, sometimes substantially.
I used to work on a system where would would bulk load some data every night. The job that did so temporarily disabled all foreign key constraints and dropped the indexes, and ran in about 1/5th of the time that it took when the constraints were enabled and the indexes were in place. (Re-enabling the constraints and rebuilding the indexes used up a good chunk of the time saved loading the data; the net gain was about 50% improvement in the bulk load time.)
Properly used, each foreign key constraint imposes a performance penalty on many insert/update/delete operations; the more foreign keys on a table, the more penalty. Improperly used, even one foreign key can severly hurt insert/update/delete performance.
Consider two tables, MASTER and DETAIL; there is a one-to-many relation between MASTER and DETAIL, and every DETAIL record has to have a master. The MASTER table has a primary key named MASTER_ID, and a data column USER_NAME. The DETAIL table has a DETAIL_ID primary key, an OWNING_MASTER_ID column that has a foreign key constraint linking it to MASTER.MASTER_ID and is NOT NULL, and a DATE column for dates and an EVENT column for a description of what happened.
Every INSERT into DETAIL must validate the foreign key constraint and therefore check the that the value of OWNING_MASTER_ID actually exists in the MASTER table. Every UPDATE of the DETAIL table must also make sure that either no change is occuring to OWNING_MASTER_ID, or that the new value of OWNING_MASTER_ID exists in the MASTER table. And finally, every DELETE on the MASTER table must check that there are no records in the DETAIL table that have the MASTER record's MASTER_ID as the DETAIL record's OWNING_MASTER_ID.
That doesn't sound too bad, however the setup I have described above will perform well for INSERT and UPDATE, but badly for DELETE of MASTER records. The problem is that there is no index on OWNING_MASTER_ID; each and every record in DETAIL has to be checked every time one record is deleted from MASTER. The problem is resolved by adding a non-unique index on the OWNING_MASTER_ID column.
It's practically an absolute requirement with foreign key constraints on tables with more than a tiny number of rows that the columns at both ends of the constraint be indexed (a primary key column is implicitly indexed).
Maintining all those indexes takes up more space in the database and further slows down INSERT/UPDATE/DELETE operations, sometimes substantially.
I used to work on a system where would would bulk load some data every night. The job that did so temporarily disabled all foreign key constraints and dropped the indexes, and ran in about 1/5th of the time that it took when the constraints were enabled and the indexes were in place. (Re-enabling the constraints and rebuilding the indexes used up a good chunk of the time saved loading the data; the net gain was about 50% improvement in the bulk load time.)
| It is sorta covered in the JavaRanch Style Guide. |