完整性约束的SQL定义

╰+攻爆jí腚メ 2022-03-19 08:44 300阅读 0赞

# [完整性约束的SQL定义][SQL]:[http://www.cnblogs.com/2007/archive/2007/08/24/868428.html][SQL] #

更新数据库时，表中不能出现不符合完整性要求的记录，以保证为用户提供正确、有效的数据。实现该目的最直接的方法，是在编写数据库应用程序时，对每个更新操作都进行完整性检查。但这种检查往往是复杂、重复、低效的。  
  
SQL把各种完整性约束作为数据库模式定义的一部分，由数据库管理系统维护，这样即可有效防止对数据库的意外破坏，提高了完整性检测的效率，又减轻了编程人员的负担。  
  
 SQL Server支持三种完整性约束：  
1、实体完整性  
2、参照完整性（或引用完整性）  
3、用户自定义完整性  
  
**1、实体完整性和主码**  
  
实体完整性是通过主码（PRIMARY KEY）的定义来实现的。一旦某个属性或属性组被定义为主码，该主码的每个属性就不能为空值，并且在表中不能出现主码值完全相同的两个记录。  
  
主码可以在CREATE TABLE语句中使用PRIMARY KEY定义。有两种定义主码的方法：一种是在属性后增加关键字，另一种是在属性表中加入额外的定义主码的子句：PRIMARY KEY（主码属性名表）。  
  
（1）属性后增加关键字定义  
CREATE TABLE StudentInfo  
(  
StudentID char(8) PRIMARY KEY,  
StudentName varchar(10),  
StudentSex bit  
);  
（2）加入额外的定义主码的子句  
CREATE TABLE StudentInfo  
(  
StudentID char(8)，  
StudentName varchar(10),  
StudentSex bit,  
PRIMARY KEY(StudentID)  
);  
如果表的主码只含有单个属性，上面的两种方法都可以使用。如果主码由多个属性组成，只能使用第二种方法。  
  
除了主码，SQL提供了类似候选码的说明方法，使用关键字UNIQUE定义（与候选码不同的是：定义为UNIQUE的属性可以定义为空值，但只能有一个记录该属性的值为NULL），说明该属性（或属性组）的值不能重复。  
  
一个表中只能有一个主码，但可以有多个“UNIQUE”定义。  
  
**2、参照完整性（引用完整性）约束和外部码**  
  
在更新记录时，参照完整性保持表之间已定义的关系。参照完整性基于外键与主键之间或外键与唯一键之间的关系。参照完整性确保键值在所有表中一致。这样的一致性要求不能引用不存在的值，如果键值更改了，那么在整个数据库中，对该键值的所有引用要进行一致的更改。

强制参照完整性时，SQL Server 禁止用户进行下列操作：  
<1>当主表中没有关联的记录时，将记录添加到相关表中。  
<2>更改主表中的值并导致相关表中的记录孤立。  
<3>从主表中删除记录，但仍存在与该记录匹配的相关记录。  
  
（1）外部码约束的说明  
说明外部码的方法有两种：  
<1>在该属性的说明（属性名、类型）后直接加上关键字REFERENCES，后跟对应表的主码说明  
格式为：REFERENCES<父表名>(<属性名>)  
说明RelationInfo表中StudentID为外部码，参照关系为StudentInfo。  
CREATE TABLE RelationInfo  
(  
RelationID int identity(1, 1),  
StudentID char(8) REFERENCES StudentInfo(StudentID),  
DepartID char(4)  
);  
< 2>在CREATE TABLE 语句的属性清单后，加上外部码的说明子句  
格式为：FOREIGN (<属性名表>)REFERENCES<父表名>(<属性名表>)

CREATE TABLE RelationInfo  
(  
RelationID int identity(1, 1),  
StudentID char(8) ,  
DepartID char(4) ,  
FOREIGN KEY (StudentID) REFERENCES StudentInfo(StudentID)  
);  
（2）参照完整性约束的实现策略  
当用户的操作违反了上述规则时，SQL 提供了两种可选方案供数据库实现者使用：RESTRICT（限制策略）；CASCADE(级联策略)；  
<1>限制策略  
限制策略是SQL的默认策略，任何违反参照完整性的更新均被系统拒绝。  
<2>级联策略  
当用户删除或更新外键所指向的键时，SQL提供了另一种方案，即级联策略。  
通过在REFERENCES子句后添加ON DELETE 和 ON UPDATE子句实现：  
 \[ ON DELETE \{ CASCADE | NO ACTION \} \]  
\[ ON UPDATE \{ CASCADE | NO ACTION \} \]  
  
如果没有指定ON DELETE 或 ON UPDATE，则默认为NO ACTION。

ON DELETE NO ACTION  
指定如果试图删除某行，而该行含有由其它表的现有行中的外键所引用的键，则产生错误并回滚 DELETE。  
  
ON UPDATE NO ACTION  
指定如果试图更新某行中的键值，而该行含有由其它表的现有行中的外键所引用的键，则产生错误并回滚 UPDATE。  
  
CASCADE 允许在表间级联键值的删除或更新操作，这些表的外键关系可追溯到执行修改的表。不能为任何具有 timestamp 列的外键和主键指定 CASCADE。  
  
ON DELETE CASCADE  
指定如果试图删除某行，而该行含有由其它表的现有行中的外键所引用的键，则也将删除所有包含那些外键的行。如果在目标表上也定义了级联引用操作，则对从那些表中删除的行同样采取指定的级联操作。  
  
ON UPDATE CASCADE  
指定如果试图更新某行中的键值，而该行的键值由其它表的现有行中的外键所引用，则所有外键值也将更新成为该键指定的新值。如果在目标表上也定义了级联引用操作，则对在那些表中更新的键值同样采取指定的级联操作。  
  
**3、用户自定义完整性约束**  
  
SQL提供非空约束、对属性的CHECK约束、对元组的CHECK约束、触发器等来实现用户的完整性要求。  
<1>基于属性的CHECK约束  
使用CHECK（检查）子句可保证属性值满足某些前提条件。CHECK子句的一般格式为：  
CHECK<条件>  
属性的CHECK约束既可跟在属性的定义后，也可在定义语句中另增一子句加以说明。  
设定StudentInfo表中age值不能小于18、大于65。只需将age属性说明为如下形式：  
age int CHECK(age >= 18 and age <= 65)  
< 2>基于元组的约束  
CREATE TABLE salary  
(  
Eno char(4),  
Basepay decimal(7, 2),  
Insure decimal(7, 2),  
Fund decimal(7, 2),  
CHECK (Insure + Fund < Basepay)  
);  
上面例子中，CHECK约束涉及到表中多个属性，为元组约束。

**约束的更新**  
  
约束与数据库中的表、视图等一样，可以进行增加、删除和修改的更新操作。为了更新约束，需要在定义约束是对约束进行命名，在约束前加上关键字CONSTRAINT和该约束的名称。  
例如要说明StudentInfo表中的主码时，将其命名为PK\_StudentInfo\_ID  
CREATE TABLE StudentInfo  
(  
StudentID char(8),  
StudentName varchar(10),  
StudentSex bit,  
CONSTRAINT PK\_StudentInfo\_ID PRIMARY KEY(StudentID)  
);  
可以使用ALTER TABLE语句来更新与属性或表有关的约束。  
（1）删除约束：  
ALTER TABLE DROP CONSTRAINT 约束名  
（2）增加约束  
ALTER TABLE ADD CONSTRAINT 约束名 约束定义

[SQL]: http://www.cnblogs.com/2007/archive/2007/08/24/868428.html