SQL FOREIGN KEY Constraint

A FOREIGN KEY constraint in SQL is used to link two tables together, enforcing referential integrity. It ensures that the values in a column of one table (the foreign key) match the values in the primary key of another table.

FOREIGN KEY: Definition and Usage

FOREIGN KEY constraints are essential for maintaining relationships between tables in a relational database. They prevent actions that would destroy links between tables, helping to keep your data consistent and accurate. For example, you wouldn't want to delete a customer if there are still orders associated with that customer; a foreign key constraint helps enforce this rule.

Creating FOREIGN KEY Constraints

Foreign key constraints can be added during table creation (CREATE TABLE) or later using ALTER TABLE. The syntax varies slightly depending on your database system.

Adding FOREIGN KEY During Table Creation

This example creates the 'Orders' table with a foreign key constraint linking to the 'PersonID' column in the 'Persons' table (assumes 'Persons' table already exists with a 'PersonID' column).

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

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

Adding Named FOREIGN KEY Constraints and Multi-Column Keys

You can name your foreign key constraint for better organization and create foreign keys based on multiple columns.

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)
);
      

Adding a FOREIGN KEY After Table Creation

You can add a foreign key to an existing table using ALTER TABLE.

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);
      

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
      

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;