SQL server表格怎么设置外键

转载

mob64ca13f8b166 2024-09-24 18:14:50

文章标签 SQL server表格怎么设置外键 Server SQL 外键约束 文章分类 SQL Server 数据库

外键约束(FOREIGN KEY)

外键约束(FOREIGN KEY)

SQL Server外键约束简介
SQL Server FOREIGN KEY（外键）约束语法
SQL Server FOREIGN KEY示例
外键引用的行为

删除父表中行的操作
父表中行的更新操作

概要：本文主要阐述了使用外键约束来强制表之间的参照完整性

SQL Server外键约束简介

思考如下Classes（班级）和Students（学生）表：

--班级表
CREATE TABLE dbo.Classes (
    class_id INT IDENTITY PRIMARY KEY,
    class_name VARCHAR (100) NOT NULL
);
--学生表
CREATE TABLE dbo.Students (
        student_id INT IDENTITY PRIMARY KEY,
        student_name VARCHAR(100) NOT NULL,
        class_id INT NOT NULL,--班级id
);

每个学生属于一个班级，每个班级包含0个或多个学生，班级和学生之间的关系是一对多关系。

对于Students表中的每一行，都可以通过Students表中的class_id字段在Classes表中找到对应的行。

但是，如果创建这两张表时，只是这样设置，数据库是不会认为这两张表之间是有关系的，因为你没有指定他们之间的关系，仅仅只是你在逻辑上认为他们是有一个关系。

为了加强Students和Classes表中数据之间的链接，需要在Students表格中建立外键。

外键是一个表中的一列或一组列，它唯一地标识另一个表（或在自引用的情况下标识同一个表）的行。
若要创建外键，请使用 FOREIGNKEY 约束。
以下语句删除Students表，并使用FOREIGNKEY约束重新创建它：

DROP TABLE Students;

CREATE TABLE dbo.Students (
        student_id INT IDENTITY PRIMARY KEY,
        student_name VARCHAR(100) NOT NULL,
        class_id INT NOT NULL,--班级id
        CONSTRAINT fk_class FOREIGN KEY (class_id) 
        REFERENCES dbo.Classes(class_id)
);

现在Classes表成为父表，即外键约束应用的表。Students表成为子表，即应用外键约束的表。

在上面的语句中，下面的子句创建了一个名为 fk_class 的 FOREIGN KEY 约束，该约束将Students表中的 class_id 链接到Classes表中的 class_id:

CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES dbo.Classes(class_id)

SQL Server FOREIGN KEY（外键）约束语法

创建外键约束的一般语法如下：

CONSTRAINT fk_constraint_name 
FOREIGN KEY (column_1, column2,...)
REFERENCES parent_table_name(column1,column2,..)

让我们详细研究一下这个语法。

首先，在CONSTRAINT关键字之后指定外键约束名称。约束名称是可选的，可以不写，因此可以按如下方式定义外键约束：

FOREIGN KEY (column_1, column2,...)
REFERENCES parent_table_name(column1,column2,..)

这样就没有指定外键约束的名字，这种情况下，SQL Server会自动给FOREIGN KEY约束生成一个名字。

其次，指定由逗号分隔的外键列的列表，这些列在FOREIGN KEY关键字后用括号括起来。

第三，指定外键引用的父表的名称，以及与子表中的列有链接的父表中的那些列。

SQL Server FOREIGN KEY示例

首先，在Classes表中插入几行：

INSERT INTO dbo.Classes(class_name)
VALUES
    ('2年纪一班'),--class_id自动生成为1
    ('2年纪二班'),--class_id自动生成为2
    ('3年纪一班'),--class_id自动生成为3

然后，在Students表中插入几个学生：

INSERT INTO dbo.Students(student_name,class_id)
VALUES
    ('张三',1),
    ('李四',1),
    ('王麻子',3)

语句正常执行，此时，张三和李四都是属于2年纪一班,王麻子属于3年纪一班

但是，我们尝试在表Students表中插入一个学生，这个学生对应的class_id在Classes表中不存在：

INSERT INTO dbo.Students(student_name,class_id)
VALUES('老王',4);

执行，SQL Server报错如下:

The INSERT statement conflicted with the FOREIGN KEY constraint "fk_class". The conflict occurred in database "SampleDb", table "dbo.Classes", column 'class_id'.

在本例中，学生老王的class_id为4，但是在Classes表中没有class_id为4的班级，由于(FOREIGN KEY)外键约束，SQL Server拒绝了插入并发出错误。

外键引用的行为

外键约束确保了参照完整性。这意味着您只能在父表中有对应行的情况下将行插入子表。

此外，外键约束允许你在更新或删除父表中的行时定义引用操作，如下所示：

FOREIGN KEY (foreign_key_columns)
REFERENCES parent_table(parent_key_columns)
ON UPDATE action 
ON DELETE action;

ON UPDATE 和 ON DELETE指定更新和删除父表中的行时将执行的操作。以下是允许的操作：NO ACTION, CASCADE, SET NULL, 和 SET DEFAULT

删除父表中行的操作

如果删除父表中的一行或多行，则可以设置以下操作之一：

ON DELETE NO ACTION(无动作):SQL Server引发错误，并回滚父表中行的删除操作。
ON DELETE CASCADE(级联删除):SQL Server删除子表中与从父表中删除的行相对应的行。
ON DELETE SET NULL(设为NULL):如果删除了父表中的相应行，SQL Server会将子表中的行设置为NULL。要执行此操作，外键列必须可以为NULL。
ON DELETE SET DEFAULT(设为默认值):如果删除了父表中的相应行，SQL Server会将子表中的行设置为其默认值。要执行此操作，外键列必须具有默认定义。请注意，如果未指定默认值，则可为NULL的列的默认值为NULL。

默认情况下，如果未明确指定任何操作，则SQL Server 使用ON DELETE NO ACTION。

父表中行的更新操作

如果更新父表中的一行或多行，则可以设置以下操作之一：

ON UPDATE NO ACTION:SQL Server引发错误，并回滚父表中行的更新操作。
ON UPDATE CASCADE:更新父表中的行时，SQL Server将更新子表中的相应行。
ON UPDATE SET NULL:更新父表中的相应行时，SQL Server会将子表中的行设置为NULL。请注意，外键列必须可以为NULL才能执行此操作。
ON UPDATE SET DEFAULT:SQL Server为子表中更新了父表中相应行的行设置默认值。