SQL FOREIGN KEY Constraint

A FOREIGN KEY is used to link two table together and it is also known as the name of referencing key.

Actually, A Foreign Key is a combination of columns whose values match a Primary Key in a different table.

EXAMPLE: FOREIGN KEY

"CUSTOMERS" Table

The "CUSTOMER_ID" column is the PRIMARY KEY column in the "CUSTOMERS" table.

CREATE TABLE CUSTOMERS (
   CUSTOMER_ID INT   NOT NULL PRIMARY KEY,
   NAME VARCHAR (100)    NOT NULL,
   EMAIL VARCHAR (100),
   ADDRESS CHAR (200),
   SALARY DECIMAL (100, 3)
);

"ORDERS" Table

The "CUSTOMER_ID" column in the "ORDERS" table is the FOREIGN KEY column.

CREATE TABLE ORDERS (
   OID INT   NOT NULL,
   DATE DATETIME,
   FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS (CUSTOMER_ID),
   AMOUNT DOUBLE,
   PRIMARY KEY (CUSTOMER_ID)
);

FOREIGN KEY Description

• The "CUSTOMER_ID" column in the "ORDERS" table points to the "CUSTOMER_ID" column in the "CUSTOMERS" table.

ALTER TABLE - FOREIGN KEY

To CREATE a FOREIGN KEY constraint on the "CUSTOMER_ID" column when the "ORDERS" table is already created, use the following SQL Syntax:

ALTER TABLE ORDERS
ADD FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS (CUSTOMER_ID);

DROP TABLE - FOREIGN KEY

To DROP a FOREIGN KEY constraint on the "ORDERS" table, use the following SQL Syntax:

ALTER TABLE ORDERS
DROP FOREIGN KEY;