What is difference between foreign key and reference key?
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.
A foreign key must refer to a primary key. When using REFERENCES constraint simply, then it isn't necessary that the referenced key be a primary key.
"Reference key" isn't a normal technical term in relational modeling or in SQL implementation in US English.
A foreign key "references" a key in some other table; could that be where the confusion comes from?
You don't really call something a reference key... They are the same thing... you might see the word references used for example in sqlite: you might use syntax like this to start a db of authors and books. This lets you show that one author can have many books. This tells the db that the books.author_id
(defined a couple of lines up) references author.id
CREATE TABLE 'author' (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
firstname varchar(255)
lastname varchar(255)
);
CREATE TABLE 'books' (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
author_id INTEGER,
title varchar(255),
published date,
FOREIGN KEY(author_id) REFERENCES author(id)
);
The only and most important difference between the two keywords 'FOREIGN KEY" and "REFERENCES" keywords is though both of them make the data to be child data of the parent table, the "FOREIGN KEY" is used to create a table level constraint whereas REFERENCES keyword can be used to create column level constraint only. Column level constraints can be created only while creating the table only. But table level constraints can be added using ALTER TABLE command.