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.