SQL FOREIGN KEY Constraint

The SQL Foreign Key is used to enforce the link between two tables and it will be a column or a collection of columns which refers to the primary key of another table.

SQL FOREIGN KEY Constraint ensures the referential integrity it means whenever a foreign key value is used it must reference a valid, existing primary key in the parent table. 

Here is an Orders Table, which has StatusID as Foreign Key that refers to OrderStatus Table.

Foreign Key constraint prevents to insert or update invalid data in primary table [ Orders ] which does not exist into another table [OrderStatus] and it always maintain relationship between these two tables.

Syntax: Create Foreign Key on Create Table:

The below SQL script creates a Foreign Key Constraint  ‘FK_OrderOrderStatus’ on StatusID column which refers to primary key column of another table [OrderStatus]

CREATE TABLE Orders (

    OrderID int NOT NULL,

    OrderNumber int NOT NULL,

    StatusID int, 

    PRIMARY KEY (OrderID),

    CONSTRAINT FK_OrderOrderStatus FOREIGN KEY (StatusID)

    REFERENCES OrderStatus(StatusID)

);

Create Table OrderStatus

(

       StatusID int IDENTITY(1,1) NOT NULL,

       StatusCode varchar(3) NOT NULL,

       StatusDescription varchar(50),

       CONSTRAINT [OrderStatus_PK] PRIMARY KEY CLUSTERED (StatusID ASC), 

)

Syntax: Create Foreign Key on Alter Table:

The below SQL creates a Foreign Key Constraint on a column StatusID and the Orders table is already created.

ALTER TABLE Orders

ADD CONSTRAINT FK_OrderOrderStatus

FOREIGN KEY (StatusID) REFERENCES OrderStatus(StatusID);

Syntax: Drop Foreign Key:

The below SQL is used to drop the Foreign Key constraint from Table

ALTER TABLE Orders

DROP CONSTRAINT FK_OrderOrderStatus;