Menu
I know the purpose of "Enforce foreign key constraint" in RDBMS. But is there any benefit when it's set to "NO" ?
376
A foreign key is a column (or combination of columns) in a table whose values must match values of a column in some other table. FOREIGN KEY constraints enforce referential integrity, which essentially says that if column value A refers to column value B, then column value B must exist.
Although the main purpose of a foreign key constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table.
It's a common mistake to avoid creating foreign keys in a database because they negatively impact the performance. It is true that foreign keys will impact INSERT, UPDATE and DELETE statements because they are data checking, but they improve the overall performance of a database.
Foreign Keys without the constraints You don't have to configure a foreign key constraint on a column just because it refers to another column. You could instead configure two tables such that one refers to the other, but without any defined foreign key.
In normal production, this setting should never be set to NO.
But: when you're developing, or restructuring a database, or when you do e.g. a large bulk load of data that you'll need to "sanitize" (clean up), then it can make sense to turn off foreign key constraints to allow "non-valid" data to be loaded into a table. Of course, as I said - you shouldn't keep that setting turned off for a long period of time - you should then proceed to clean up the data, either delete those rows that are in violation of the FK constraint, or update their values so they match a parent row.
So again: in "normal" production mode, this setting should never be NO - but for specific tasks, it might help get the job done more easily. Use it with caution, and always turn the FK constraints back on as soon as you can!
87
Not in everyday usage, as far as I know. The times I've de-enforced foreign keys for a while are when there are problems with data and fixing them is hidered by relationship checks.
During bulk operations constraint checks are temporarily ignored in order to increase performance.
37
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
