SQL FOREIGN KEY 约束

The FOREIGN KEY 约束用来指向另一个数据表中的主键。Foreigh key 可以用来确保数据表中的数据存在。

例如,我们有一个数据表 Employee (EmployeeId 是主键):

EmployeeId FirstName LastName
1 Mkai Khaao
2 Hiaf Lihaaa

我们还有另外一个数据表 Project:

ProjectId ProjectName ProjectManagerId
1 Basic Project 1
2 Premium Project 2

Project表中的 ProjectManagerId 指向Employee表中的EmployeeId. 这意味着只有一个存在的Employee可以成为ProjectManager. 如果你试图设置 ProjectManagerId=3, 则会失败, 因为Employee表中并没有EmployeeId=3.

创建带有 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)
)

如果你想给 foreign key 约束取一个名字:
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)
)

使用ALTER TABLE设置 FOREIGN KEY :

SQL Server / MySQL / Oracle / MS Access:

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

删除 PRIMARY KEY:

SQL Server / Oracle / MS Access:

    ALTER TABLE Project
    DROP CONSTRAINT fk_EmployeeId

My SQL:

    ALTER TABLE Orders
    DROP FOREIGN KEY fk_EmployeeId