Using Foreign Keys or Check Constraints in SQL Server

A foreign key constraint is really nothing more than a check constraint with a list of allowed values. So the question becomes, when should you use a check constraint and when should you use a foreign key?

Check constraints should be used when you need to validate patterns, perform calculation to compare against or use comparison operators such as >, <, >=, and so on.

Foreign keys should be used when you need to validate the column against a list of acceptable values, even if the list contains only one or two value, you should still implement it as a foreign key.

For Check constraint, whenever you want to add a new value to the list, you have to modify the table structure by using an ALTER TABLE. But for foreign key, you simply insert the new value in to the table.

 Using a foreign key for list validation also leads to a maintainable design. When a database is initially designed, you might not know the list of acceptable values. Or the list might be completely valid at the time it was created, but five years later the list of valid value might have changed. Application developers can easily add a maintenance screen into an application to allow one or more designated users to modify the list of allowed values and the foreign key constraints prevents a value from being removed from the table if it has been used. You don’t need to go to DBA for these small DBA tasks.