约束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 这两个系统表获取外键约束的信息

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

View Code

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?

转载于:https://www.cnblogs.com/ljhdo/p/5485068.html