SQL FOREIGN KEY Constraint

-- this table doesn't contain foreign keys
CREATE TABLE Customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER
  );

-- create another table named Products
-- add foreign key to the customer_id column
-- the foreign key references the id column of the Customers table

CREATE TABLE Products (
    customer_id INTEGER ,
    name VARCHAR(100),
    FOREIGN KEY (customer_id)
    REFERENCES Customers(id)
  );

Foreign Key Syntax in SQL

The syntax of the SQL FOREIGN KEY constraint is:

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...,
    FOREIGN KEY (column_name)
    REFERENCES referenced_table_name (referenced_column_name)
);

Here,

• table_name is the name of the table where the FOREIGN KEY constraint is to be defined
• column_name is the name of the column where the FOREIGN KEY constraint is to be defined
• referenced_table_name and referenced_column_name are the names of the table and the column that the FOREIGN KEY constraint references

Referencing Columns in Another Table with FOREIGN KEY

The FOREIGN KEY constraint in SQL establishes a relationship between two tables by linking columns in one table to those in another. For example,

Here, the customer_id field in the Orders table is a FOREIGN KEY that references the customer_id field in the Customers table.

This means that the value of the customer_id (of the Orders table) must be a value from the customer_id column (of the Customers table).

Note: The foreign key can be referenced to any column in the parent table. However, it is a general practice to reference the foreign key to the primary key of the parent table.

Creating Foreign Key

Now, let's see how we can create foreign key constraints in a database.

-- this table doesn't have a foreign key
CREATE TABLE Customers (
  id INT,
  first_name VARCHAR(40),
  last_name VARCHAR(40),
  age INT,
  country VARCHAR(10),
  CONSTRAINT CustomersPK PRIMARY KEY (id)
);

-- add foreign key to the customer_id field
-- the foreign key references the id field of the Customers table
CREATE TABLE Orders (
  order_id INT,
  product VARCHAR(40),
  total INT,
  customer_id INT,
  CONSTRAINT OrdersPK PRIMARY KEY (order_id),
  FOREIGN KEY (customer_id) REFERENCES Customers(id)
);

Here, the value of the customer_id column in the Orders table references the row in another table named Customers with its id column.

Note: The above code works in all major database systems. However, there may be an alternate syntax to create foreign keys depending on the database. Refer to their respective database documentation for more information.

Inserting Records in Table With Foreign Key

Lets try to insert records in a table with foreign keys.

-- insert record into table with no foreign key first
INSERT INTO Customers
VALUES
(1, 'John', 'Doe', 31, 'USA'),
(2, 'Robert', 'Luna', 22, 'USA');

-- insert record into table with foreign key constraint in customer_id column
-- Insertion Success
INSERT INTO Orders
VALUES
(1, 'Keyboard', 400, 2),
(2, 'Mouse', 300, 2),
(3, 'Monitor', 12000, 1);

Here, the query is successfully sql-executed as the rows we are trying to insert in the Orders table have valid values in the customer_id column, which has a FOREIGN KEY constraint in the Customers table.

Insertion Failure in Foreign Key

An insertion failure occurs when a value is entered into a table's foreign key column that does not match any value in the primary key column of the related table. For example,

-- insert record into table with no foreign key first
INSERT INTO Customers
VALUES
(1, 'John', 'Doe', 31, 'USA'),
(2, 'Robert', 'Luna', 22, 'USA');

-- insert record into table with a foreign key
-- insertion error because customer with id of 7 does not exist
INSERT INTO Orders
VALUES (4, 'Keyboard', 400, 7);

Here, the insertion of rows into the Orders table fails because 7 is not a valid customer_id value in the Customers table. Thus, this query fails the FOREIGN KEY constraint.

Why use Foreign Key?

Foreign keys are an important part of relational databases, and we use them for the following reasons:

To Normalize Data

The FOREIGN KEY constraint helps us to normalize data in multiple tables and reduce redundancy. This means that a database can have multiple tables that are related to each other.

Prevent Wrong Data From Insertion

If two database tables are related through a field (attribute), using FOREIGN KEY makes sure that wrong data is not inserted in that field. This helps eliminate bugs at the database level.

Recommended Reading: SQL JOIN

Foreign Key With Alter Table

It is possible to add the FOREIGN KEY constraint to an existing table using the ALTER TABLE command. For example,

CREATE TABLE Customers (
  id INT,
  first_name VARCHAR(40),
  last_name VARCHAR(40),
  age INT,
  country VARCHAR(10),
  CONSTRAINT CustomersPK PRIMARY KEY (id)
);

CREATE TABLE Orders (
  order_id INT,
  item VARCHAR(40),
  amount INT,
  customer_id INT,
  CONSTRAINT OrdersPK PRIMARY KEY (order_id)
);

-- add foreign key to the customer_id field of Orders
-- the foreign key references the id field of Customers
ALTER TABLE Orders
ADD FOREIGN KEY (customer_id) REFERENCES Customers(id);

Note: This action is not supported on our online SQL editor as it is based on SQLite.

Multiple Foreign Keys in a Table

A database table can also have multiple foreign keys.

For instance, let's say that we need to record all transactions where each user is a buyer and a seller.

-- this table doesn't have a foreign key
CREATE TABLE Users (
  id INT PRIMARY KEY,
  first_name VARCHAR(40),
  last_name VARCHAR(40),
  age INT,
  country VARCHAR(10)
);

-- add foreign key to buyer and seller fields
-- foreign key references the id field of the Users table
CREATE TABLE Transactions (
  transaction_id INT PRIMARY KEY,
  amount INT,
  seller INT,
  buyer INT,
  CONSTRAINT fk_seller FOREIGN KEY (seller) REFERENCES Users(id),
  CONSTRAINT fk_buyer FOREIGN KEY (buyer) REFERENCES Users(id)
);

Here, the SQL command creates two foreign keys (buyer and seller) in the Transactions table.

Note: As with other constraints, naming a FOREIGN KEY constraint using CONSTRAINT constraint_name is optional. But doing so makes it easier to make changes to or delete the constraint. This is especially helpful when defining multiple constraints.

Recommended Readings:

• SQL Constraints
• SQL PRIMARY KEY Constraint