Menu
One of the disadvantages people point to with using foreign keys in a relational database is the overhead of making sure the parent table exists before doing any insert operation. (Example). As your database and operation grows, this effect is amplified. Does anyone know if large sites on the web use them then? If so, how do they get around that extra overhead? If not, as your development team gets bigger, it seems that that could cause a lot of potential bugs/conflicts/miscommunication/orphan-rows.
Any insight?
319
Having active foreign keys on tables improves data quality but hurts performance of insert, update and delete operations. Before those tasks database needs to check if it doesn't violate data integrity. This is a reason why some architects and DBAs give up on foreign keys at all.
Foreign key columns are frequently used in join criteria when the data from related tables is combined in queries by matching the column or columns in the foreign key constraint of one table with the primary or unique key column or columns in the other table.
Foreign keys put the “relational” in “relational database” – they help define the relationship between tables. They allow developers to maintain referential integrity across their database.
A foreign key might point to data that no longer exists, or the foreign key's data type doesn't match the primary key data type, eroding referential integrity. Referential integrity can also be corrupted if the foreign key doesn't reference all the data from the primary key.
I work for a telecom, yes they do, often the cost of FK is too small compared to severity one major incidents (hours of time lost investigating, patching ...) that can be caused by accidents due to lack of FK checking.
173
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
