Description

The SQL keyword FOREIGN KEY is a constraint used to link or logically connect two related tables together.

A foreign key is nothing but a column (or a set of columns) from a table that refers to the primary key on another table.

This keyword is used while creating or altering a table as shown below.

CREATE TABLE With FOREIGN KEY

The below CREATE TABLE statement creates a new table with a FOREIGN KEY constraint as shown below.

  • The column "order_id" is defined as a primary key.
  • The column "customer_id" is defined as a foreign key, referring to the column "customer_id" on the "customers" table.
-- Syntax for MySQL database system
CREATE TABLE orders (
    order_id INT NOT NULL,
    order_value INT NOT NULL,
    customer_id INT,
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
-- Syntax for SQL Server or Oracle or MS Access database systems
CREATE TABLE orders (
    order_id INT NOT NULL PRIMARY KEY,
    order_value INT NOT NULL,
    customer_id INT FOREIGN KEY REFERENCES customers(customer_id)
);

Use the below example if we need to define a name or define a FOREIGN KEY constraint on multiple columns.

-- Syntax for MySQL, SQL Server, Oracle, or MS Access database systems
CREATE TABLE orders (
    order_id INT NOT NULL,
    order_value INT NOT NULL,
    customer_id INT,
    PRIMARY KEY (order_id),
    CONSTRAINT FK_CustomerOrder FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

ALTER TABLE With FOREIGN KEY

The below ALTER TABLE statement alters the existing table definition by adding a FOREIGN KEY constraint as shown below.

  • The column "order_id" is defined as a primary key.
  • The column "customer_id" is defined as a foreign key, referring to the column "customer_id" on the "customers" table.
ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

Use the below example if we need to define a name or define a FOREIGN KEY constraint on multiple columns.

ALTER TABLE orders
ADD CONSTRAINT FK_CustomerOrder
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

DROP FOREIGN KEY

The below SQL deletes a FOREIGN KEY constraint, which is already defined on the table.

-- Syntax for MySQL database system.
ALTER TABLE orders
DROP FOREIGN KEY FK_CustomerOrder;
-- Syntax for SQL Server, Oracle, and MS Access database systems.
ALTER TABLE orders
DROP CONSTRAINT FK_CustomerOrder;

Related Links