约束5:外键约束
在关系型数据库中,表与表之间存在引用关系,也就是说,数据列C1引用其他表的数据列C2中存在的值,引用关系通过外键(Foreign Key )约束实现。如果表(TableA)中的列C1被其他表引用,那么,我们把表(TableA)称作参考表,或引用表(Referenced Table),该列C1是其他表的参考列,或引用列(Referenced Column),对引用列执行Update 或 Delete 操作会受到很多限制。某些情况下,也把参考表称作父表,把引用父表的列称作子列,或外键列。
[ FOREIGN KEY ] REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
例如,在数据列C1和C2之间创建外键约束,C1是引用列,C2列引用C1列的值,外键约束的特点:
- 如果C2列的值不是NULL,那么C2列的值必须存在于C1列中,否则,系统返回违反外键约束的错误;
- 外键约束可以创建在同一个表的不同列之间,这种引用类型称作自引用;
- 外键约束可以引用单列,也可以引用多列;
- 外键约束只能引用那些构成主键或唯一约束的数据列,或者唯一索引键列,也就是说,外键约束的引用列(单列或复合列)必须是唯一的;
一,外键约束的级联操作
当引用列的值被更新(UPDATE或DELETE)时,子列会采取相应的动作:
- 默认行为是NO ACTION:如果子列引用父表的值,那么父表的更新操作被回滚,数据库抛出错误;
- 级联操作(CASCADE):当父表删除数据行A时,子表删除引用该数据行A的所有行;当父表更新数据行B时,子表把引用该数据行B的所有数据行都更新为新值;
- 置空(SET NULL):当父表更新数据行A时,子表把引用该数据行A的所有数据行都设置为NULL,该设置要求外键列必须可空(nullable);
- 设置默认值(SET DEFAULT):当父表更新数据时,子表把引用该数据行的数据行都设置为外键列的默认值,该设置要求外键列必须有默认值定义,如果构成外键的某一个列,没有显式定义默认值,并且可为NULL,那么系统把NULL作为列的默认值。
这些级联操作是系统预定义的,不能更改。
二,手动更新外键
在对引用列进行更新操作(Update 和 Delete)之前,必须禁用外键约束,然后手动更新,最后再次启用外键约束。手动更新外键,给用户提供了很大的灵活性,由用户保证外键约束的有效性。
1,查看外键列的信息
使用 sys.foreign_keys 和 sys.foreign_key_columns 这两个系统表获取外键约束的信息
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select fk.object_id as FK_Object_ID, fk.name as FK_name, pt.name as ParentTable_Name, pc.name as ParentTable_Column_Name, rt.name as ReferencedTable_Name, rc.name as ReferencedTable_Column_Name from sys.foreign_keys fk with(nolock) inner join sys.foreign_key_columns fkc with(nolock) on fk.object_id=fkc.constraint_object_id inner join sys.tables pt with(nolock) on fkc.parent_object_id=pt.object_id inner join sys.columns pc with(nolock) on fkc.parent_object_id=pc.object_id and fkc.parent_column_id=pc.column_id inner join sys.tables as rt with(nolock) on fkc.referenced_object_id=rt.object_id inner join sys.columns rc with(nolock) on fkc.referenced_object_id=rc.object_id and fkc.referenced_column_id=rc.column_id where rt.name=N'Referenced_Table_name
2,禁用外键列
--disable FK constraint alter table ParentTable_name nocheck constraint FK_name
将外键 nocheck之后,可以使用 Delete 或 Update 命令更新数据,但是,当使用 Truncate 命令时,依然会报错
Cannot truncate table 'xxx' because it is being referenced by a FOREIGN KEY constraint.
3,启用外键列
--enable FK constraint alter table ParentTable_name check constraint FK_name
三,扩展引用完整性
系统自定义的外键约束,由很多限制,例如,引用列和外键列必须在相同的服务器上,相同的数据库中,不支持跨数据库的外键约束。而外键约束,实际上,就是一列引用另外一列的值,这个功能可以由触发器来实现,用于扩展引用完整性。触发器的实现过程是:创建INSTEAD OF触发器,在插入数据之前,检查插入的数据是否存在于参考表中,如果存在,插入成功;如果不存在,回滚事务,抛出异常。
CREATE TRIGGER schema_name.trigger_name ON table_name INSTEAD OF { [ INSERT ] [ , ] [ UPDATE ] } AS { sql_statement [ ; ] [ ,...n ] }
在DML触发器中,有两个特殊的系统表inserted和deleted,用于表示插入的新数据和删除的旧数据。
参考文档:
Use the inserted and deleted Tables
How can I list all foreign keys referencing a given table in SQL Server?