SQL FOREIGN KEY Constraint

by

The FOREIGN KEY constraint is used to point to primary key of another database table. Foreigh key ensures that the data exists when we have reference to it.

For example, we have a table of Employee (EmployeeId is Primary):

EmployeeId FirstName LastName
1 Mkai Khaao
2 Hiaf Lihaaa

And we have another table Project:

ProjectId ProjectName ProjectManagerId
1 Basic Project 1
2 Premium Project 2

The ProjectManagerId in table Project points to EmployeeId in table Employee. That means only an exist employee can be as a project manager. If you try to set ProjectManagerId=3, it will fail, because it doesn’t have an employee with EmployeeId=3.

To create a table with FOREIGN KEY:

SQL Server / Oracle / MS Access

CREATE TABLE Project
(
    ProjectID int NOT NULL PRIMARY KEY,
    ProjectName varchar(100) NOT NULL,
    ProjectManagerId int FOREIGN KEY REFERENCES Employee(EmployeeId)
)

My SQL

CREATE TABLE Project
(
    ProjectId int NOT NULL,
    ProjectName varchar(100) NOT NULL,
    ProjectManagerId int,
    PRIMARY KEY (ProjectId ),
    FOREIGN KEY (ProjectManagerId) REFERENCES Employee(EmployeeId)
)

If you want to name a foreign key constraint:
MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Project
(
    ProjectId int NOT NULL,
    ProjectName varchar(100) NOT NULL,
    ProjectManagerId int,
    PRIMARY KEY (ProjectId ),
    CONSTRAINT fk_EmployeeId FOREIGN KEY (ProjectManagerId )
    REFERENCES Employee(EmployeeId)
)

To set FOREIGN KEY by using ALTER TABLE:

SQL Server / MySQL / Oracle / MS Access:

    ALTER TABLE Project
    ADD FOREIGN KEY (ProjectManagerId )
    REFERENCES Employee(EmployeeId)

To drop a PRIMARY KEY:

SQL Server / Oracle / MS Access:

    ALTER TABLE Project
    DROP CONSTRAINT fk_EmployeeId

My SQL:

    ALTER TABLE Orders
    DROP FOREIGN KEY fk_EmployeeId

Leave a Comment