SQL 语言：完整性约束

概述

数据库的完整性是指数据库正确性和相容性，是防止合法用户使用数据库时向数据库加入不符合语义的数据。保证数据库中数据是正确的，避免非法的更新。数据库完整性重点需要掌握的内容有：完整性约束条件的分类、完整性控制应具备的功能。

完整性约束类别大概分为实体完整性约束、参照完整性约束、自定义完整性约束三类。

实体完整性约束：非空值约束（NOT NULL）、默认值（DEFAULT）、唯一性约束（UNIQUE、UNIQUE(列名)）、主键约束（PRIMARY KEY 、PRIMARY KEY(列名)）。

参照完整性约束：外键约束（REFERENCES 表名(列)、FOREIGN KEY(列名) REFERENCES 表名(列)）。

自定义完整性约束：约束表达式（CHECK）。

其中，非空值约束和默认值只有列级完整性约束；唯一性约束、主键约束、外键约束、CHECK 有表级和列级完整性约束。

主键 ( Primary Key ) 约束

完整性约束条件作用的对象有关系、元祖、列三种，又分静态和动态，共分为六类：

在关系中只能有一个主键。声明主键有两种方法：

1. 将 PRIMARY KEY 保留字加在属性类型之后（列级完整性约束）。

1. 在属性列表中引入新元素，该元素包含保留字 PRMARY KEY和利用圆括号括起形成该键的属性或属性组列表（表级完整性约束）。

当主键有多个属性时必须用方法2，表级完整性约束。

示例1. 学生关系Students(Sno,Sname,Sex,Sdept,Sage)的主键是Sno，在创建学生关系时可使用PRIMARYKEY进行实体完整性约束。创建学生表的SQL语句如下：

CREATE TABLE Students
(
  Sno CHAR(8)
  ,Sname CHAR(10)
  ,Sex CHAR(1)
  ,Sdept CHAR(20)
  ,Sage NUMBER(3)
  ,PRIMARY KEY(Sno)
);

外键（Foreign Key）约束

参照完整性定义语法如下：

FOREIGN KEY (属性名) REFERENCES 表名(属性名)
[ON DELETE [CASCADE|SET NULL]

关键字说明：FOREIGN KEY 定义哪些列为外码；REFERENCES 指明外码对应于哪个表的主码；ON DELETE CASCADE 指明删除被参照关系的元组时，同时删除参照关系中的元组；SET NULL 表示置为空值方式。

示例2. 对于示例5学生选课关系SC(Sno, Cno, Grade)中，学号Sno 参照关系Students， 课程号Cno 参照关系C。因此完整的语句为：

CREATE TABLE SC
(
  Sno CHAR(8)
  ,Cno CHAR(4)
  ,Grade NUMBER(3)
  ,PRIMARY KEY(Sno)
  ,PRIMARY KEY(Cno)
  ,FOREIGN KEY Sno REFERENCES Students(Sno)
  ,FOREIGN KEY Cno REFERENCES C(Cno)
);

属性值上的约束

属性值上的约束可以通过 NOT NULL、UNIQUE 和 CHECK 进行：

NOT NULL：在 SQL 中，NULL 值是所有域的成员，也是每个属性默认的合法值。但是，根据用户要求有些属性不允许取空值，此时可用“NOT NULL”进行约束。例如，银行的账户关系Account(Account-no, branch-name, balance)不允许余额balance取空值，此时可用“balance numeric(12,2) not null” 进行约束，即禁止在该属性上插入一个空值。

UNIQUE：唯一标识数据库表中的每条记录。

CHECK：CHECK 子句可用于保证属性值满足指定的条件，条件与 where 类似。例如，银行关系 Branch (branch-name,branch-city,assets) 要求资产 assets 不能为负值 ，此时可用“CHECK (assets >=0)”进行约束。

示例3. 学生关系Students(Sno,Sname,Sex,Sdept,Sage)，假设用户要求学生姓名不能为空，男生的年龄为1525岁，女生的年龄为1523岁。那么可使用如下语句创建表:

CREATE TABLE Students
(
  Sno CHAR(8)
  ,Sname CHAR(10) NOT NULL
  ,Sex CHAR(1)
  ,Sdept CHAR(20)
  ,Sage NUMBER(3)
  ,PRIMARY KEY(Sno)
  ,CHECK (Sage >= 15
          AND ((Sex = 'M' AND Sage <= 25)
                OR (Sex = 'F' AND Sage <= 23))
);

全局约束

全局约束是指一些比较复杂的完整性约束，这些约束涉及多个属性间的联系或多个不同关系间的联系。

基于元组的检查子句和断言两种情况：

（1）基于元组的检查子句：这种约束是对单个关系的元组值加以约束。方法是在关系定义中的任何地方加上关键字 CHECK 和约束条件。

例如，年龄在16至20岁之间，可用 CHECK(Sage>=16 AND Sage<=20) 检测。

（2）基于断言的语法格式

格式

CREATE ASSERTION <断言名> CHECK(<条件>)

示例4. 教学数据库的模式Students、SC、C中创建一个约束ASSE-SC1:不允许男同学选修“张勇”老师的课。

CREATE ASSERTION ASSE_SC1 
CHECK (NOT EXISTS 
                (SELECT * 
                 FROM SC 
                 WHERE Cno IN (SELECT Cno 
                               FROM C 
                               WHERE TEACHER = '张勇')
                       AND Sno IN (SELECT Sno 
                                   FROM Students 
                                   WHERE SEX = 'M')));

示例9. 教学数据库的模式Students、SC、C中创建 一个约束ASSE_SC2；每门课最多允许50名男同学选修。

CREATE ASSERTION ASSE_SC2 
CHECK (50>=ALL(SELECT COUNT(SC.Sno)
               FROM Students,SC
               WHERE Students.Sno = SC.Sno 
                     AND SEX = 'M'
               GROUP BY Cno));

总结

SQL完整性约束是维护数据库中数据准确性和一致性的一组关键机制。通过应用这些约束，我们能够确保操作数据库时数据的有效性和业务的规范性得到维护。总结来说，SQL完整性约束对于任何数据库系统都是至关重要的，它们不仅仅保证了数据的质量和可信度，还提高了数据库的性能和安全性。作为数据库设计人员或开发者，合理地使用这些约束，可以在很大程度上避免数据的混乱和错误，提升整个系统的稳定性和可靠性。