sql - What is difference between foreign key and reference key?

Question

I am very confused about those two terms. Are they the same or different?

Some books and people say they are the same and others say they are different.

I tried but couldn't find a conclusive answer.

question from:https://stackoverflow.com/questions/8595695/what-is-difference-between-foreign-key-and-reference-key

Answer 1

I am supposing that you are talking about using the REFERENCES where the FOREIGN KEY keyword is not used when constraining a column inline, which is called a column-level foreign key constraint, eg.

author_id INTEGER REFERENCES author(id)

... instead of the table-level foreign key constraint, which is placed after the column declarations ...

author_id INTEGER,
FOREIGN KEY(author_id) REFERENCES author(id)

The answer is, that it is simply shorthand syntax for the same thing. The main concern when altering between the two should be readability.

For more advanced use, it might be relevant that only table-level foreign key constraints can describe constraints on multiple keys at once, where all must be present in the referenced table.

---

Do note that MySQL 'parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification', meaning that only the table-level foreign key constraint will work.

Both Postgres and Microsoft's SQL Server respect both column- and table-level foreign key constraints.