SQL 参照完整性

定义参照完整性

• 关系模型的参照完整性在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码，用REFERENCES短语指明这些外码参照哪些表的主码。
• 例如，关系SC中一个元组表示一个学生选修的某门课程的成绩，(Sno,Cno）是主码，Sno,Cno分别参照引用Student表的主码和Course表的主码。

[例5.3]：定义SC中的参照完整性。

CREATE  TABLE SC
 (
     Sno CHAR(9) NOT NULL,
     Cno CHAR(4) NOT NULL,
     Grade SMALLINT,
     PRIMARY KEY (Sno,Cno),      /*在表级定义实体完整性*/
     FOREIGN KEY (Sno) REFERENCES Student(Sno),/*在表级定义参照完整性*/
     FOREIGN KEY (Cno) REFERENCES Course(Cno)/*在表级定义参照完整性*/
);

参照完整性检查和违约处理

• 参照完整性将两个表中的相应元素联系起来了，因此，对被参照表和参照表进行增，删，改操作时有可能破坏参照完整性，必须进行检查以保证这两个表的相容性。
• 例如，对表SC和Student有4种可能破坏参照完整性的情况，如下表所示：

被参照表（例如Student)	参照表（例如SC)	违约处理
可能破坏参照完整性	插入元组	拒绝
可能破坏参照完整性	修改外码值	拒绝
删除元组	可能破坏参照完整性	拒绝/级联删除/设置为空值
修改主码值	可能破坏参照完整性	拒绝/级联删除/设置为空值

• SC表中增加一个元组，该元组的Sno属性值在表Student中找不到一个元组，其Sno属性值与之相等。
• 修改SC表中的一个元组，修改后该元组的Sno属性值在表Student中找不到一个元组，其Sno属性值与之相等。
• 从Student表中删除一个元组，造成SC表中某些元组的Sno属性值在表Student中找不到一个元组，其Sno属性值与之相等。
• 修改Student表中一个元组的Sno属性，造成SC表中某些元组的Sno属性值在表Student中找不到一个元组，其Sno属性值与之相等。
• 当上述的不一致发生时，系统可以采用以下策略加以处理。
  • 拒绝（NO ACTION）执行。
    • 不允许该操作执行，该策略一般设置为默认策略。
  • 级联（CASCADE）操作。
    • 当删除或修改被参照表（Student）的一个元组导致与参照表（SC）的不一致时，删除或修改参照表中的所有道义不一致的元组。
    • 例如，删除Student表中Sno值为"201215121"的元组，则从要SC表中级联删除SC.Sno='201215121’的所有元组。
  • 设置为空值。
    • 当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值，例如，有下面两个关系：
    • 学生（学号，姓名，性别，专业号，年龄）
    • 专业（专业号，专业名）
    • 其中学生关系的"专业号"是外码，因为专业号是专业关系的主码。
    • 假设专业表中的某个元组被删除，专业号为12，按照设置为空值的策略，就要把学生表中专业号=12的所有元组的专业号设置为空值，这对应了这样的语义：某个专业删除了，该专业的所有学生专业未定，等待重新分配专业。
• 这里讲解一下外码能否接收空值的问题。
• 例如，学生表中"专业号"是外码，按照应用的实际情况可以取空值，表示这个学生的专业尚未确定，但在学生----选课数据库中，关系Student为被参照关系，其主码为Sno,SC为参照关系，Sno为外码，它能否取空值呢?答案是否定的，因为Sno为SC的主属性，按照实体完整性Sno不能为空值，若SC的Sno为空值，则表明尚不存在的某个学生，或按照实体完整性Sno不能为空值，若SC的Sno为空值，则表明尚不存在的某个学生，或者某个不知学号的学生，选修了某门课程，其成绩记录在Grade列中，这与学校的应用环境是不相符的，因此SC的Sno列不能取空值，同样SC的Cno是外码，也是SC的主属性，也不能取空值。
• 因此对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值。
• 一般地，当对参照表和被参照表的操作违反了参照完整性时，系统选用默认策略，即拒绝执行，如果想让系统采用其他策略则必须在创建参照表时显式地加以说明。

[例5.4]：显式说明参照完整性的违约处理示例。

CREATE TABLE SC
     (
         Sno CHAR(9),
         Cno CHAR(4),
         Grade SMALLINT,
         PRIMARY KEY (Sno,Cno),/*在表级定义实体完整性，Sno,Cno都不能取空值*/
         FOREIGN KEY (Sno) REFERENCES Student(Sno)/*在表级定义参照完整性*/
             ON  DELETE CASCADE /*当删除Student表中的元组时，级联删除SC表中相应元组*/
             ON  UPDATE  CASCADE ,/*当更新Student表中的sno时，级联更新SC表中的相应的元组*/
         FOREIGN KEY (Cno) REFERENCES Course(Cno)/*在表级定义参照完整性*/
             ON DELETE NO ACTION /*当删除Course表中的元组造成与SC表不一致时，拒绝删除*/
             ON UPDATE  CASCADE /*当更新Course表中的Cno时，级联更新SC表中相应的元组*/
);

• 可以对DELETE和UPDATE采用不同的策略，例如，例5.4中当删除被参照表Course表中的元组，造成与参照表（SC表）不一致时，拒绝删除被参照表的元组，对更新操作则采取级联更新的策略。
• 从上面的讨论可以看到，关系数据库管理系统在实现参照完整性时，除了要提供定义主码，外码的机制外，还需要提供不同的策略供用户选择，具体选择哪种策略，要根据应用环境的要求确定。