What is the difference between check and foreign key?

Question

i am quite confused about the difference between a FOREIGN KEY and CHECK constraint - they appear to me to achieve the same result.

I mean I could create a table and enforce a Foreign key on another table, but i could create a CHECK to ensure the value in in another table.

What is the difference and when to use the one or the other?

Answer 1

A FOREIGN KEY constrain ensures that the entry DOES EXISTS in

EDIT another table

as per correct comment Exists in another table... or the same table. – Mark Byers

A CHECK constrain ensures that the entry follows some rule.

CHECK Constraints

CHECK constraints enforce domain integrity by limiting the values that are accepted by a column. They are similar to FOREIGN KEY constraints in that they control the values that are put in a column. The difference is in how they determine which values are valid: FOREIGN KEY constraints obtain the list of valid values from another table, and CHECK constraints determine the valid values from a logical expression that is not based on data in another column.

Answer 2

A foreign key constraint is more powerful than a CHECK constraint.
A foreign key constraint means that the column (in the current table) can only have values that already exist in the column of the foreign table (which can include the be the same table, often done for hierarchical data). This means that as the list of values changes - gets bigger or smaller - there's no need to update the constraint.

A check constraint can not reference any columns outside of the current table, and can not contain a subquery. Often, the values are hard coded like BETWEEN 100 and 999 or IN (1, 2, 3). This means that as things change, you'll have to update the CHECK constraint every time. Also, a foreign key relationship is visible on an Entity Relationship Diagram (ERD), while a CHECK constraint will never be. The benefit is that someone can read the ERD and construct a query from it without using numerous DESC table commands to know what columns are where and what relates to what to construct proper joins.

Best practice is to use foreign keys (and supporting tables) first. Use CHECK constraints as a backup for situations where you can't use a foreign key, not as the primary solution to validate data.

Answer 3

It depends on your DBMS (which you didn't specify), but in one sense, you are correct: a foreign key constraint is a particular case of a check constraint. There are DBMS which would not allow you to formulate a foreign key constraint as a check constraint.

The main intention of a check constraint is to describe conditions that apply to a single row in the table. For example, I have a table of elements (as in Hydrogen, Helium, ...) and the symbols for the elements are constrained to start with an upper-case letter and are followed by zero, one or two lower-case letters (two lower-case letters for as yet undiscovered but predicted elements: Uus - ununseptium (117), which has just been isolated but has yet to be named). This can be the subject of a CHECK constraint:

CHECK(Symbol MATCHES "[A-Z][a-z]{0,2}")

assuming MATCHES exists and supports an appropriate regular expression language.

You can also have check constraints that compare values:

CHECK(OrderDate <= ShipDate OR ShipDate IS NULL)

To express a foreign key constraint as a check constraint, you have to be permitted to execute a query in the CHECK clause. Hypothetically:

CHECK(EXISTS(SELECT * FROM SomeTable AS s
              WHERE ThisTable.pk_col1 = s.pk_col1 AND
                    ThisTable.pk_col2 = s.pk_col2))

This example shows some of the problems. I don't have a convenient table alias for the table in which I'm writing the check constraint - I assumed it was 'ThisTable'. The construct is verbose. Assuming that the primary key on SomeTable is declared on columns pk_col1 and pk_col2, then the FOREIGN KEY clause is much more compact:

FOREIGN KEY (pk_col1, pk_col2) REFERENCES SomeTable

Or, if you are referencing an alternative key, not the primary key:

FOREIGN KEY (pk_col1, pk_col2) REFERENCES SomeTable(ak_col1, ak_col2)

This is notationally more compact - so there is less chance of getting it wrong - and can be special-cased by the server because the special notation means it knows that it is dealing with a foreign key constraint whereas the general check clause has to be scrutinized to see if it matches one of many possible forms that are equivalent to the foreign key.

The question asks: when to use a check constraint and when to use a foreign key constraint?

• Use a CHECK constraint to specify criteria that can be checked in a single row.
• Use a FOREIGN KEY constraint to specify that the values in the current row must match the values of a row in some other unique key (a candidate key, usually the primary key rather than an alternative key) of some table - which may be the same table or (more usually) a different table.