SQL Server数据完整性学习笔记

桃扇骨 2022-11-15 11:53 207阅读 0赞

### SQL Server数据完整性学习笔记 ###

这是一篇早期的学习笔记，转发到这里。

数据完整性也称为完整性约束，关系数据库的最大优点之一就是模型本身集成了数据完整性。作为模型的一部分而实施的数据完整性（也就是说，作为表定义的一部分）称为声明式（declarative）数据完整性。【还可以用代码来实施的数据完整性（例如，用存储过程或触发器）称为过程式（procedural）数据完整性，在此不介绍。】

声明式约束包括字段（列）的数据是否允许为NULL值、主键、唯一约束（UNIQUE）、外键、检查约束（CHECK），以及DEFAULT约束。当用CREATE TABLE语句创建表时，可以同时定义这些约束；或者在已经创建好表之后，用ALTER TABLE语句增加这些约束。字段（列）的数据数据类型也是声明式约束。

除了DEFAULT约束以外，其他所有约束都可以定义为组合约束（即基于一个或多个属性的约束）。

【切换数据库，use  数据库名  
创建数据库，create database 数据库名】

先在数据库中创建一个名为Employees的表备用：

CREATE TABLE dbo.Employees
    (
        empid INT NOT NULL,
        firstname VARCHAR(30) NOT NULL,
        lastname VARCHAR(30) NOT NULL,
        hiredate DATETIME NOT NULL,
        mgrid INT NULL,
        ssn VARCHAR(20) NOT NULL,
        salary MONEY NOT NULL
    );

【**注**：DBO是database owner的简写，被意为：数据库所有者。  
DBO是每个数据库的默认用户，具有所有者权限。不指定schema的表等对象，都默认为dbo。】

一、主键约束（Primary Key Constraints）

主键约束实施行的唯一约束，同时不允许约束属性取NULL值。约束属性中每一组唯一的值在表中只能出现一次，换句话说，表内的每行数据可以被唯一确定。如果试图在允许NULL值的列上定义主键约束，RDBMS会拒绝。每个表只能定义一个主键。下面以前面创建的Employees表为例，在它的empid列上定义一个主键约束：

ALTER TABLE dbo.Employees
    ADD CONSTRAINT PK_Employees PRIMARY KEY(empid)

定义好主键以后，就可以保证所有的empid值将是唯一而确定的。如果插入或更新一行违反数据约束的数据，RDBMS就会拒绝操作，生成一个报错。

为了实施逻辑主键约束的唯一约束，SQL Server将在幕后创建一个唯一索引（unique index）。唯一索引是SQL Server为了实施唯一约束而采用的一种物理机制。也可以用索引（不一定是唯一索引）来加速查询的处理，避免对整个表进行不必要的扫描（类似于图书的索引）。

二、唯一约束（Unique Constraints）

唯一约束用来保证数据行的一个列（或一组列）数据的唯一，可以在数据库中实现关系模型的替换键（alternate key）的概念。与主键不同的是，在同一个表中可以定义多个唯一约束。此外，唯一约束也不限于只定义在NOT NULL列上。ANSI SQL支持两种类型的唯一约束，一种是只允许在唯一约束列中有一个列可以为NULL值，另一种则允许多个NULL值列。SQL Server只实现了前者。

以下代码在Employees表中定义了ssn列上的一个唯一约束：

ALTER TABLE dbo.Employees
    ADD CONSTRAINT UNQ_Employees_ssn UNIQUE(ssn);

主键一样，SQL Server也在幕后创建一个唯一索引，作为实施逻辑唯一约束的物理机制。

三、外键约束

外键约束用于实施引用完整性。这种约束在引用表（referencing table）的一组属性上进行定义，并指向被引用表（referenced table）中的一组候选键（主键或唯一约束）。注意：引用表和被引用表可能是同一个表。外键的目的是为了将外键列允许的值域限制为被引用列中现有的值。

以下代码创建了一个名为Orders的表，其主键定义在orderid列上：

CREATE TABLE dbo.Orders
    (
        orderid INT NOT NULL,
        empid INT NOT NULL,
        custid VARCHAR(10) NOT NULL,
        orderts DATETIME NOT NULL,
        qty INT NOT NULL,
        CONSTRAINT PK_Orders PRIMARY KEY(orderid)
    );

如果现在想实施一个完整性规则，将Orders表的empid列支持的值域限制为现有的Employees表中empid列的值。为此，要在Orders表的empid列上定义一个外键约束，让它指向Employees表的empid列，如下所示:

ALTER TABLE dbo.Orders
    ADD CONSTRAINT FK_Orders_Employees  FOREIGN KEY(empid) REFERENCES dbo.Employees(empid)

类似地，如果想限制Employees表的mgrid列支持的值域为同一个表中已经存在的那些empid列的值，可以增加以下外键约束：

ALTER TABLE dbo.Employees
    ADD CONSTRAINT FK_Employees_Employees FOREIGN KEY(mgrid) REFERENCES Employees(empid)

注意：即使被引用的候选键列不存在NULL值，在外键列中也允许NULL值（例如Employees表中的mgrid）。

前面两个外键的定义实施的引用操作称为“禁止操作（no action）“。禁止操作的含义是：当试图删除被引用表中的行，或更新被引用的候选键时，如果在引用表中存在相关的行，则此操作不能执行。例如，如果试图从Employees表中删除一个雇员数据行，而Orders表中同时还存在与这个雇员相关的订单数据行，RDBMS将拒绝执行这样的操作，并生成报错信息。

**可以定义具有级联操作的外键，以补偿这样的操作****----****为了当在引用表中存在相关的数据行时，可以删除被引用表中的数据行或更新被引用候选键属性。可以在外键定义中将****ON DELETE****和****ON UPDATE****选项定义为****CASCADE****、****SET DEFAULT****和****SET NULL****之类的操作。**CASCADE的含义是：操作（删除或更新）将被级联到引用表中相关行。例如，ON DELETE CASCADE意味着当从被引用表中删除一行时，RDBMS也将从引用表中删除相关的行。SET DEFAULT和SET NULL意味着补偿操作会把相关行的外键属性分别设置为列的默认值或NULL值。注意：不论选择哪种操作，如果遇到了前面提及的NULL值异常，引用表将只有孤立的数据行。对此可参见[https://blog.csdn.net/cnds123/article/details/116176679][https_blog.csdn.net_cnds123_article_details_116176679]

四、检查约束（Check）

检查约束用于定义表中输入或修改一行数据之前必须满足的一个谓词。例如，以下的检查约束可以保证Employees表的salary列只支持正数：

ALTER TABLE dbo.Employees
    ADD CONSTRAINT CHK_Employees_salary CHECK(salary>0);

如果试图用非正数的salary值插入或更新数据行，RDBMS将拒绝这样的操作。注意：当谓词计算结果为FALSE时，检查约束将拒绝插入或更新数据行的操作。当谓词计算结果为TRUE或UNKNOWN时，RDBMS将会接受对数据行的修改。例如：salary为\-1000将被拒绝，而salary为50000和NULL都可以被接受。

当增加CHECK和FOREIGN KEY约束时，可以指定一个WITH NOCHECK选项，告诉RDBMS不必对现有的数据进行约束检查。通常认为这是一种不好的实践做法，因为这样不能保证数据的一致性。

也可以禁用或激活现有的CHECK和FOREIGN KEY约束。

五、默认约束（Default）

默认约束与特定的属性关联。当插入一行数据时，如果没有为属性显示指定明确的值，就可以用一个表达式作为其默认值。例如，以下代码为orderts属性定义了一个默认约束（表示订单的时间戳）：

ALTER TABLE dbo.Orders
    ADD CONSTRAINT DFT_Orders_orderts DEFAULT(CURRENT_TIMESTAMP) FOR orderts;

认表达式会调用CURRENT\_TIMESTAMP函数，由它返回当前的日期和时间值。在定义好默认表达式以后，当在Orders表插入一行数据，而且没有显示指定orderts属性时，SQL Server将把这个属性值设置为CURRENT\_TIMESTAMP。

**附：约束管理**

**特别提醒**：如果一定要用程序方式来更改约束，只能先删除约束，再新建。可以使用ALTER TABLE命令为表添加约束，其中的WITH \{ CHECK | NOCHECK \} 即WITH CHECK | WITH NOCHECK 用来指定表中的数据是否用新添加的或重新启用的 FOREIGN KEY 或 CHECK 约束进行验证——修改之前是否检查现有数据是否符合要指定的约束。如果未指定，对于新约束，假定为 WITH CHECK，对于重新启用的约束，假定为 WITH NOCHECK。

对于已有一批不满足约束条件要求的历史遗留数据，既想原样保留又需要为未来的数据添加约束，这时创建约束就需要带有WITH NOCHECK选项。下面的示例将在表中的现有列中添加一个约束。该列包含一个违反约束的值。因此，将使用 WITH NOCHECK 以避免根据现有行验证该约束，从而允许添加该约束。

禁用和启用约束

\{ CHECK | NOCHECK\} CONSTRAINT \{ ALL | constraint\_name \[ ,...n \] \}

指定启用或禁用 constraint\_name。此选项只能与 FOREIGN KEY 和 CHECK 约束一起使用。如果指定了 NOCHECK，则将禁用约束，从而在将来插入或更新列时，不根据约束条件进行验证。无法禁用 DEFAULT、PRIMARY KEY 和 UNIQUE 约束。

删除约束

DROP \{ \[ CONSTRAINT \] constraint\_name | COLUMN column\_name \}

[https_blog.csdn.net_cnds123_article_details_116176679]: https://blog.csdn.net/cnds123/article/details/116176679