1、SQL Server 外键约束的例子外键约束的测试表与测试数据- 创建测试主表. ID 是主键.CREATE TABLE test_main (id INT,value VARCHAR(10),PRIMARY KEY(id) );- 创建测试子表. CREATE TABLE test_sub (id INT,main_id INT,value VARCHAR(10),PRIMARY KEY(id) );- 插入测试主表数据.INSERT INTO test_main(id, value) VALUES (1, ONE);INSERT INTO test_main(id, value) VALU
2、ES (2, TWO);- 插入测试子表数据.INSERT INTO test_sub(id, main_id, value) VALUES (1, 1, ONEONE);INSERT INTO test_sub(id, main_id, value) VALUES (2, 2, TWOTWO);默认外键约束方式1 ALTER TABLE test_sub ADD CONSTRAINT main_id_cons FOREIGN KEY (main_id) REFERENCES test_main;2 go1 DELETE2 test_main3 WHERE4 ID = 1;5 go消息 547
3、，级别 16，状态 1，服务器 HOME-BED592453CSQLEXPRESS，第 1 行DELETE 语句与 REFERENCE 约束“main_id_cons“冲突。该冲突发生于数据库“Stock“，表“dbo.test_sub“, column main_id。语句已终止。测试完毕后，删除 外键约束ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;DELETE CASCADE 方式1 - 创建外键( 使用 ON DELETE CASCADE 选项，删除主表的时候，同时删除子表)2 ALTER TABLE test_sub3 ADD C
4、ONSTRAINT main_id_cons4 FOREIGN KEY (main_id) REFERENCES test_main ON DELETE CASCADE;56 - 测试删除主表数据. 将成功地执行.7 DELETE8 TEST_MAIN9 WHERE10 ID = 1;1112 - 测试检索子表，应该只有一条 main_id = 2 的数据 .13 SELECT14 *15 FROM16 test_sub;1718 go(1 行受影响 )id main_id value- - -2 2 TWOTWO(1 行受影响 )测试完毕后，删除 外键约束 ALTER TABLE test_
5、sub DROP CONSTRAINT main_id_cons;UPDATE CASCADE 方式- 创建外键(使用 ON UPDATE CASCADE 选项，更新主表的主键时候，同时更新子表外键)1 ALTER TABLE test_sub2 ADD CONSTRAINT main_id_cons3 FOREIGN KEY (main_id) REFERENCES test_main ON UPDATE CASCADE;4 go- 测试更新主表数据. 将成功地执行.1 UPDATE test_main SET id = 5 where id = 12 go(1 行受影响 )- 检查 子表
6、数据是否被同步更新1 select * from test_sub2 goid main_id value- - -1 5 ONEONE2 2 TWOTWO测试完毕后，删除 外键约束 ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;SET NULL 方式1 - 创建外键( 使用 ON DELETE SET NULL 选项，删除主表的时候，同时将子表的main_id 设置为 NULL)2 ALTER TABLE test_sub3 ADD CONSTRAINT main_id_cons4 FOREIGN KEY (main_id) REFERE
7、NCES test_main ON DELETE SET NULL;567 - 测试删除主表数据. 将成功地执行.8 DELETE9 TEST_MAIN10 WHERE11 ID = 2;1213 - 测试检索子表，应该有一条的 main_id = null14 SELECT15 *16 FROM17 test_sub;1819 go(1 行受影响 )id main_id value- - -2 NULL TWOTWO(1 行受影响 )测试完毕后，删除 外键约束 ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;SET DEFAULT 方式 为
8、了更容易理解， SET DEFAULT 方式 使用下面这样的 测试表 与 测试数据. - 创建测试主表 . ID 是主键.- 班级表 .CREATE TABLE test_main_class (id INT NOT NULL,value VARCHAR(20),PRIMARY KEY(id) );- 创建测试子表 . - 学生表 .CREATE TABLE test_sub_student (id INT NOT NULL,main_id INT DEFAULT 0,value VARCHAR(10),PRIMARY KEY(id) );- 插入测试主表数据.INSERT INTO test
9、_main_class(id, value) VALUES (0, 暂无班级);INSERT INTO test_main_class(id, value) VALUES (1, 2013 级 1 班);INSERT INTO test_main_class(id, value) VALUES (2, 2013 级 2 班);- 插入测试子表数据.INSERT INTO test_sub_student(id, main_id, value) VALUES (1, 1, 张三);INSERT INTO test_sub_student(id, main_id, value) VALUES (2
10、, 2, 李四);首先测试 ON DELETE SET DEFAULTALTER TABLE test_sub_studentADD CONSTRAINT fk_main_classFOREIGN KEY (main_id) REFERENCES test_main_class ON DELETE SET DEFAULT;SELECTtest_main_class.value AS “班级“,test_sub_student.value AS “学生“FROMtest_main_classJOIN test_sub_studentON(test_main_class.id = test_sub
11、_student.main_id);go班级 学生- -2013 级 1 班 张三2013 级 2 班 李四(2 行受影响 )1 DELETE FROM test_main_class WHERE ID = 1;2 go(1 行受影响 )1 SELECT2 test_main_class.value AS “班级“,3 test_sub_student.value AS “学生“4 FROM5 test_main_class6 JOIN test_sub_student7 ON(test_main_class.id = test_sub_student.main_id);8 go班级 学生-
12、-暂无班级 张三2013 级 2 班 李四(2 行受影响 )1ON DELETE SET DEFAULT指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则组成被引用行中的外键的所有值将被设置为它们的默认值。为了执行此约束，目标表的所有外键列必须具有默认定义。如果某个列可为空值，并且未设置显式的默认值，则将使用 NULL 作为该列的隐式默认值。因 ON DELETE SET DEFAULT 而设置的任何非空值在主表中必须有对应的值，才能维护外键约束的有效性。然后测试 ON UPDATE SET DEFAULT- 删除前面的外键约束.ALTER TABLE test_sub_st
13、udent DROP CONSTRAINT fk_main_class; GO- 创建外键约束.ALTER TABLE test_sub_studentADD CONSTRAINT fk_main_classFOREIGN KEY (main_id) REFERENCES test_main_class ON UPDATE SET DEFAULT;- 更新主表.1 UPDATE test_main_class SET ID = 20 WHERE ID = 2;2 go(1 行受影响 )12 SELECT3 test_main_class.value AS “班级“,4 test_sub_stu
14、dent.value AS “学生“5 FROM6 test_main_class7 JOIN test_sub_student8 ON(test_main_class.id = test_sub_student.main_id);9 go班级 学生- -暂无班级 张三暂无班级 李四(2 行受影响 )ON UPDATE SET DEFAULT指定如果试图更新某一行，而该行的键被其他表的现有行中的外键所引用，则组成被引用行中的外键的所有值将被设置为它们的默认值。为了执行此约束，目标表的所有外键列必须具有默认定义。如果某个列可为空值，并且未设置显式的默认值，则将使用 NULL 作为该列的隐式默认值。因 ON UPDATE SET DEFAULT 而设置的任何非空值在主表中必须有对应的值，才能维护外键约束的有效性。
