7 实现数据完整性

SQL 学习复习资料(微软)

第7章 章 实现数据完整性

SQL 学习复习资料(微软)

SQL Server 2005数据库开发与实现 数据库开发与实现第1章：SQL Server 2005启航 章 启航 第2章：安装和配置 章 安装和配置SQL Server 2005 第3章：创建数据库和数据 章 库文件 第4章：创建数据类型和表 章 第5章：使用 章 使用Transact-SQL查 查 询数据库 第6章：创建和优化索引 章 第7章：实现数据完整性 章 第8章：实现视图 章 第9章：实现存储过程和函 章 数 第10章：使用 XML 章 第11章：灾难恢复 章 第12章：管理安全性 章 第13章：监视 SQL Server 章 第14章：维持高可用性 章 第15章：自动执行管理任务 章 第16章：使用 Service Broker 章 第17章：传输数据 章 第18章：复制功能简介 章

SQL 学习复习资料(微软)

第7章：实现数据完整性 章数据完整性概述 实现约束 实现触发器 实验 习题

SQL 学习复习资料(微软)

数据完整性的类型

7.1.1 数据完整性的类型

域(或列)完整性 或列)

实体(或行) 实体(或行)完整性

引用完整性 在表或者同一个表的列之间) (在表或者同一个表的列之间)

SQL 学习复习资料(微软)

实施数据完整性的选项

7.1.2 实施数据完整性的选项

机制 数据类型 规则 默认值 约束 触发器 XML 构架

描述定义可存储在列中的数据类型 定义可插入到列中的可接受的值 定义如果值没有指定的时候列的值 定义数据库引擎如何强制数据完整性 定义当表被修改的时候自动执行的代码 定义 XML 文档的命名空间、结构和可接受内容

SQL 学习复习资料(微软)

第7章：实现数据完整性 章数据完整性概述 实现约束 实现触发器 实验 习题

SQL 学习复习资料(微软)

约束

7.2.1 约束

完整性类 型

约束类型DEFAULT 指定列的默认值 指定列的允许值 指定必须存在值的列 指定是否允许为NULL 唯一标识每一行 防止非主键重复

描述

域

CHECK FOREIGN KEY NULL

实体

PRIMARY KEY UNIQUE FOREIGN KEY

引用CHECK

定义值与同一个表或另一个表的主键值匹配的一 列或多列组合 指定根据同一个表中其他列的值可在列中接受的 数据值

SQL 学习复习资料(微软)

创建约束

7.2.2 创建约束

CREATE TABLE语句创建约束 语句创建约束 ALTER TABLE语句修改现有表上的约束 语句修改现有表上的约束CREATE TABLE table_name ({ < column_definition > | < table_constraint > } [ ,...,n ]) < column_definition > ::= {column_name data_type } [{DEFAULT constant_expression | [ IDENTITY [( seed , increment )]]}] [ < column_constraint > [ n ] ] < column_constraint > ::=

SQL 学习复习资料(微软)

PRIMARY KEY 约束

7.2.3 PRIMARY KEY 约束

PRIMARY KEY 约束定义表中构成主键的一列或多 列 一个表只能有一个 PRIMARY KEY 约束 指定为 PRIMARY KEY 的列中的值必须是唯一的 包含在 PRIMARY KEY 约束中的列不能接受 NULL值 值CREATE TABLE [HumanResources].[Department]( [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL, [Name] [dbo].[Name], … CONSTRAINT [PK_Department_DepartmentID] PRIMARY KE

Y CLUSTERED ([DepartmentID] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] )

SQL 学习复习资料(微软)

DEFAULT 约束

7.2.4 DEFAULT 约束

当没有提供值的时候， 当没有提供值的时候，DEFAULT 为用户定义一个默 认的列值。 认的列值。 每列只允许一个DEFAULT 约束 每列只允许一个 只适用于INSERT 约束 只适用于 允许系统提供的一些值CREATE TABLE [Production].[Location]( ... [Availability] [decimal](8, 2) NOT NULL CONSTRAINT [DF_Location_Availability] DEFAULT ((0.00)), [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Location_ModifiedDate] DEFAULT (getdate()) )

SQL 学习复习资料(微软)

CHECK 约束

7.2.5 CHECK 约束

CHECK 约束限制在 INSERT 和 UPDATE 语句期间用 户可输入特定列中的数据值 每列可以定义多个CHECK约束 每列可以定义多个 约束 在同一个表格中可以引用列 不能包含子查询ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] WITH CHECK ADD CONSTRAINT [CK_EmployeeDepartmentHistory_EndDate] CHECK (([EndDate]>=[StartDate] OR [EndDate] IS NULL))

SQL 学习复习资料(微软)

使用Transact-SQL语句 语句 使用 创建DEFAULT约束 创建 约束

7.2.6 使用 使用Transact-SQL语句 语句 创建DEFAULT约束 创建 约束

修改[HumanResources].[EmployeeDepartmentHistory]表 表 修改 向表中添加名为 CK_EmployeeDepartmentHistory_EndDate的CHECK约束 的 约束 确保EndDate列的值大于等于 列的值大于等于StartDate列的值，或者 列的值， 确保 列的值大于等于 列的值 EndDate为NULL 为

ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] WITH CHECK ADD CONSTRAINT [CK_EmployeeDepartmentHistory_EndDate] CHECK (([EndDate]>=[StartDate] OR [EndDate] IS NULL))

SQL 学习复习资料(微软)

UNIQUE约束 约束

7.2.7 UNIQUE约束 约束

UNIQUE约束确保列中的每个值都是唯一的 约束确保列中的每个值都是唯一的 在约束列中允许一个NULL值 值 在约束列中允许一个 可以包含一列或者多列

CREATE TABLE [HumanResources].[Employee]( [EmployeeID] [int] IDENTITY(1,1) NOT NULL, [NationalIDNumber] [nvarchar](15) NOT NULL UNIQUE NONCLUSTERED, … )

SQL 学习复习资料(微软)

FOREIGN KEY 约束

7.2.8 FOREIGN KEY 约束

FOREIGN KEY 约束确保同一个表或者不同表之间 的引用完整性 必须引用一个PRIMARY KEY或者 必须引用一个 或者UNIQUE约束 约束 或者 用户必须在应用表上具有REFERENCES权限 权限 用户必须在应用表上具有

ALTER TABLE [Sales].[SalesOrderHeader]

WITH CHECK

ADD CONSTRAINT [FK_SalesOrderHeader_Customer_CustomerID] FOREIGN KEY([CustomerID]) REFERENCES [Sales].[Customer] ([CustomerID])

SQL 学习复习资料(微软)

级联引用完整性

7.2.9 级联引用完整性

FOREIGN KEY 约束包含一个 CASCADE 选项 Cascade 选 项 NO ACTION (Default) CASCADE SET NULL SET DEFAULT

UPDATE 行为

DELETE 行为

引发错误；回滚操作 在引用表中更新外键 在引用表中删除行

在引用表中设置外键为NULL 在引用表中设置外键为DEFAULT值

SQL 学习复习资料(微软)

约束检查的注意事项

7.2.10 约束检查的注意事项

为约束指定有

意义的名称 无需删除以及重新创建表，即可创建、 无需删除以及重新创建表，即可创建、更改或删除 约束 在你的应用程序和事务中执行错误检查 禁用 CHECK 和 FOREIGN KEY 约束 约束: 当运行大型批处理作业时提高性能 当向表中添加新的约束的时候避免检查已经存 在的数据

SQL 学习复习资料(微软)

课堂练习： 课堂练习：创建约束

本次课堂练习的目标是能够使用 Transact-SQL创建约束。 创建约束。 创建约束

SQL 学习复习资料(微软)

第7章：实现数据完整性 章数据完整性概述 实现约束 实现触发器 实验 习题

SQL 学习复习资料(微软)

触发器

7.3.1 触发器

当执行INSERT、UPDATE或者 、 或者DELETE语句修改一张 当执行 或者 语句修改一张 表格的时候指定存储过程 两类触发器: 两类触发器 AFTER 触发器在执行 INSERT、UPDATE 或 DELETE 语句的操作之后执行 INSTEAD OF触发器代替INSERT、 UPDATE或 DELETE 语句执行 触发器和初始化语句属于一个事务的一部分

SQL 学习复习资料(微软)

INSERT 触发器的 工作方式

7.3.2 INSERT 触发器 的工作方式

1 2 3

执行INSERT语句 语句 执行 记录INSERT语句 语句 记录 执行AFTER INSERT触发器语句 执行 触发器语句

CREATE TRIGGER [insrtWorkOrder] ON [Production].[WorkOrder] AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO [Production].[TransactionHistory]( [ProductID],[ReferenceOrderID],[TransactionType] ,[TransactionDate],[Quantity],[ActualCost]) SELECT inserted.[ProductID],inserted.[WorkOrderID] ,'W',GETDATE(),inserted.[OrderQty],0 FROM inserted; End

SQL 学习复习资料(微软)

DELETE 触发器的 工作方式

7.3.3 DELETE 触发器 的工作方式

1 2 3

执行DELETE语句 执行 语句 记录DELETE 语句 记录 执行AFTER DELETE 触发器语句 执行

CREATE TRIGGER [delCustomer] ON [Sales].[Customer] AFTER DELETE AS BEGIN SET NOCOUNT ON; EXEC master..xp_sendmail @recipients=N'SalesManagers@', @message = N'Customers have been deleted!!'; END;

本文来源：https://www.bwwdw.com/article/xcw4.html