SQL Server Performance Forum – Threads Archive
CheckConstraint vs. Foreign Key.
Anyone have any clue as to the benefit or lack thereof, on using a check constraint vs a foreign key for data validation..? We have 2 tables, and one already has 3 constraints on it, and NO foreign key. We need to add some sort of validation that could launch an error if someone entered a record for a payment_type that did NOT exists in the Payment_type table. I know with triggers if you have too many that can be a performance buster, but is there any issue with having multiple constraints on a table..? -David-David Roesch
San Diego, Ca
From a design point of view it’s much better to have an FK to a lookup Payment_Type table, and I’d expect the performance not to be significantly worse, although the more expert experts might disagree. Tom Pullen
DBA, Oxfam GB
Hi ya, check constraints are really designed to check for deterministic conditions, such as checking that a column value is in a known and static list of values foreign keys are to ensure that relationships between tables are kept. This works in both directions, so updating the parent table will check the child table, and vice versa triggers are way more flexible and allow the above two plus more For me these three options are used on a horses for courses basis, but listed in order of preference for performance and ease of maintenance Cheers
Twan
Use Constraints for data integrity and foreign keys for any business rules validations and as mentioned by Twan it is bit confusing way when you’re learning these stuff in a training course. In the real world it depends on how the database is designed and application fetches the data. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thank you all The data is fairly static, payments have only changed once for us in 3 years so most likely well move forward with the Constraint. -David
-David Roesch
San Diego, Ca
Also foreign keys help to document your database, in terms of how your tables potentially link to each other.
]]>