外键约束(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为子表中更新了父表中相应行的行设置默认值。