SQL FOREIGN KEY Constraint Example

The constraint FOREIGN KEY defines a relationship between the data of two tables, using a column or a combination of columns.

  • It establishes a logical relationship between the data on a table with the primary key on another table.

In the below example, the column supplier_id on the table orders is defined using the FOREIGN KEY keyword.

  • Column customer_id is defined as a FOREIGN KEY with the column customer_id on the customers table.
  • Column shipper_id is defined as a FOREIGN KEY with the column customer_id on the shippers table.

Run this on IDE

CREATE TABLE orders (
    order_id INT NOT NULL PRIMARY KEY,
    order_date DATE NOT NULL,
    order_value INT,
    customer_id INT FOREIGN KEY REFERENCES customers(customer_id),
    shipper_id INT FOREIGN KEY REFERENCES shippers(shipper_id)
);

In case the table already exists, it returns an error message, which can be avoided by including the keyword IF NOT EXISTS as shown below.

CREATE TABLE IF NOT EXISTS orders (
    order_id INT NOT NULL PRIMARY KEY,
    order_date DATE NOT NULL,
    order_value INT,
    customer_id INT FOREIGN KEY REFERENCES customers(customer_id),
    shipper_id INT FOREIGN KEY REFERENCES shippers(shipper_id)
);

Where the tables customers and shippers are defined as mentioned below.

CREATE TABLE customers (
    customer_id INT NOT NULL PRIMARY KEY,
    customer_name VARCHAR(30) NOT NULL,
    address VARCHAR(20),
    city VARCHAR(20),
    country VARCHAR(20) NOT NULL,
    postal_code VARCHAR(10)
);
CREATE TABLE shippers (
    shipper_id INT NOT NULL PRIMARY KEY,
    shipper_name VARCHAR(30) NOT NULL,
    phone VARCHAR(15) NOT NULL UNIQUE
);

Overall

We now know when and how to use the FOREIGN KEY constraint.

Related Links