SQL FOREIGN KEY Constraint

A FOREIGN KEY constraint in SQL is used to link two tables together, ensuring that the data in one table (the child table) is related to the data in another table (the parent or referenced table). This is crucial for maintaining data integrity and preventing inconsistencies.

FOREIGN KEY: Definition and Usage

A foreign key is a column (or set of columns) in one table that refers to the primary key of another table. The table with the foreign key is called the child table; the table with the primary key is the parent table. The foreign key constraint enforces a relationship: a value in the child table's foreign key column must match an existing value in the parent table's primary key column. This prevents you from inserting or updating data that would create a link to a non-existent record in the parent table.

Example Tables

Consider these 'Persons' and 'Orders' tables:

Persons Table

PersonID	LastName	FirstName	Age
1	Hansen	Ola	30
2	Svendson	Tove	23
3	Pettersen	Kari	20

Orders Table

OrderID	OrderNumber	PersonID
1	77895	3
2	44678	3
3	22456	2
4	24562	1

Notice that the 'PersonID' column in the 'Orders' table refers to the 'PersonID' column in the 'Persons' table. 'PersonID' is a primary key in 'Persons' and a foreign key in 'Orders'.

Creating FOREIGN KEY Constraints

Adding FOREIGN KEY During Table Creation

You can add a foreign key constraint when you create a table using CREATE TABLE. The syntax differs slightly depending on the database system.

Syntax (MySQL)


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

Syntax (SQL Server, Oracle, MS Access)


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

Output

Creates the 'Orders' table with a foreign key constraint linking 'PersonID' in 'Orders' to 'PersonID' in 'Persons'.

Adding Named Constraints and Multi-Column Foreign Keys

For better organization, you can name your constraint and define it across multiple columns.

Syntax


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

Output

Creates the 'Orders' table with a named foreign key constraint.

Adding a FOREIGN KEY After Table Creation

Use ALTER TABLE to add a foreign key to an existing table.

Syntax


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

--Named constraint:
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Output

Adds a foreign key constraint to the 'Orders' table.

Dropping FOREIGN KEY Constraints

To remove a foreign key constraint, use DROP CONSTRAINT (most systems) or DROP FOREIGN KEY (MySQL).

Syntax (MySQL)


ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

Syntax (SQL Server, Oracle, MS Access)


ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;

Output

The foreign key constraint 'FK_PersonOrder' is removed.