What is difference between foreign key and reference key?

In terms of standard SQL, both result in a foreign key constraint.

One form is a _table constraint_, meaning it can apply to one or more columns. You would need this to reference a table that has a multi-column primary key:

CREATE TABLE child (
id int PRIMARY KEY,
parent_id int,
date date,
FOREIGN KEY (parent_id, date) REFERENCES parent(id, date)
);

The other form is a _column constraint_, meaning it can only apply to the single column it is defined with. It cannot be used to reference a table with a multi-column primary key.

CREATE TABLE child (
id int PRIMARY KEY,
parent_id int REFERENCES parent(id)
);

The above syntax works exactly the same as if you declared a table constraint for a single column (supposing the RDBMS supports this type of column constraint), as follows:

CREATE TABLE child (
id int PRIMARY KEY,
parent_id int,
FOREIGN KEY (parent_id) REFERENCES parent(id)
);


It frequently causes confusion for users of MySQL and its InnoDB storage engine, that the latter column-constraint style is not supported. You must define a table-level constraint for a foreign key, even if it is a single-column constraint. This has been a strange behavior of MySQL since its earliest days, that some constraint syntax is valid, but results in no constraint. See discussion here:

[To see links please register here]