Foreign Key in SQL

Relational databases are categorized as they are because the data is stored in tables that are related to each other in some way. The relationship occurs because a row in one table may be directly related to one or more rows in another table.

images/articles/mysql/foreign-key-in-sql.jpg

A FOREIGN KEY is a key used to link two tables together. It is a field (or collection of fields) in one table that refers to the Primary Key in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

The Foreign Key is used to prevent actions that would destroy links between tables. It also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

For example, the "id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table. The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

CREATE TABLE Orders (
id INT NOT NULL,
OrderNumber INT NOT NULL,
PersonID INT,
PRIMARY KEY (id),
FOREIGN KEY (PersonID) REFERENCES Persons(id)
);

To create a Foreign Key constraint on the table that is already created,

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(id);