《SQL创建约束》PPT课件.ppt

数据库程序设计 SQL Server 2000 数据库程序设计,第1章 SQL Server 概述 第2章 创建和管理数据库 第3章 创建数据类型和表 第4章 实现数据完整性 第5章 Transact-SQL介绍 第6章 使用 Transact-SQL查询工具 第7章 检索数据 第8章 数据分组与汇总 第9章 多表联接 第10章 子查询 第11章 修改数据,第12章 全文索引查询 第13章 规划索引 第14章 创建和维护索引 第15章 实现视图 第16章 实现存储过程 第17章 实现用户定义函数 第18章 实现触发器 第19章 多服务器编程 第20章 优化查询性能 第21章 分析查询 第22章 管理事务和锁,第4章 实现数据完整性,数据完整性的类型 强制数据完整性 定义约束 约束的类型 禁用约束 使用默认值和规则 决定使用何种强制方法 推荐操作,数据完整性的类型,4.1 数据完整性的类型,数据完整性 指的是数据库中存储的数据的一致性和准确性 数据完整性的类型：域完整性、实体完整性、引用完整性 域完整性 域（或列）完整性是指对列指定一组有效的值并决定是否可为空值 实体完整性 实体（或表）完整性要求表中的所有行都有一个惟一的标识符，称为主键值 引用完整性 引用完整性确保主键（在被引用表中）和外键（在引用表中）之间的关系得到保持,第4章 实现数据完整性,数据完整性的类型 强制数据完整性 定义约束 约束的类型 禁用约束 使用默认值和规则 决定使用何种强制方法 推荐操作,强制数据完整性,声明式数据完整性 作为对象定义的一部分来定义数据必须达到的标准 SQL Server 自动强制完整性 通过使用约束、默认和规则来实现 过程式数据完整性 在脚本中定义数据必须达到的标准 在脚本中强制完整性 通过使用触发器和存储过程来实现 可在客户端或服务器用其他编程语言和工具来实现,4.2 强制数据完整性,第4章 实现数据完整性,数据完整性的类型 强制数据完整性 定义约束 约束的类型 禁用约束 使用默认值和规则 决定使用何种强制方法 推荐操作,定义约束,决定使用何种约束 创建约束 使用约束的注意事项,4.3 定义约束,决定使用何种约束,4.3.1 决定使用何种约束,创建约束,使用 CREATE TABLE 或者 ALTER TABLE CREATE TABLE 是在创建表时创建约束 ALTER TABLE 是在一个已有的表上创建约束 可以添加约束到已有数据的表上 可添加单列或多列约束 若约束应用于单列，称为列级约束 若约束引用了多列，称为表级约束，即使它并没有引用表中的所有列,4.3.2 创建约束,可直接在表上创建、更改和删除约束，而不必删除并重建表 应该在应用程序和事务内建立错误检查逻辑，以测试是否违反了某个约束 SQL Server 里的约束只是“最后防线” 当给一个表添加约束的时候，SQL Server 将检查现有数据是否违反约束 建议创建约束的时候指定名称，否则系统将为约束自动产生一个复杂的名称 名称必须惟一，且符合 SQL Server 标识符的规则 查看约束的信息 系统存储过程：sp_helpconstraint、sp_help 信息模式视图：check_constraints、referential_constraints、table_constraints 系统表：syscomments、sysreferences、sysconstraints,4.3.3 使用约束的注意事项,第4章 实现数据完整性,数据完整性的类型 强制数据完整性 定义约束 约束的类型 禁用约束 使用默认值和规则 决定使用何种强制方法 推荐操作,约束的类型,DEFAULT 约束 CHECK 约束 PRIMARY KEY 约束 UNIQUE 约束 FOREIGN KEY 约束 级联引用完整性,4.4 约束的类型,DEFAULT 约束,如果一个列的值在 INSERT 语句中没有指定，DEFAULT 约束将自动输入一个值，可以是预先指定的常量、NULL 或者一个系统函数运行时的值 语法：CONSTRAINT 约束名 DEFAULT 约束表达式 创建 DEFAULT 约束的两种方法 创建一个默认（CREATE DEFAULT），然后使用存储过程 sp_bindefault 将默认绑定到一个列 CREATE TABLE 或 ALTER TABLE 时使用 DEFAUTL 约束 应用 DEFAULT 约束的注意事项 DEFAULT 约束创建时将检查表中的现存数据 DEFAULT 约束只对 INSERT 语句有效 每列只能定义一个 DEFAULT 约束 不能和“标识”属性及 rowversion 数据类型共同使用,4.4.1 DEFAULT 约束,DEFAULT 约束（续）,应用 DEFAULT 约束的注意事项（续） 允许使用一些系统提供的值：USER、CURRENT_USER、SESSION_USER、SYSTEM_USER 及 CURRENT_TIMESTAMP 有可能会和 CHECK 约束冲突 为具有 PRIMARY KEY 或 UNIQUE 约束的列指定默认值是没有意义的 常量值外面可以加或者不加括号，字符或者日期常量必须加上单引号或双引号 在一个 INSERT 语句中对一个列,4.4.1 DEFAULT 约束,DEFAULT 约束（续）,DEFAULT 约束示例 为 Northwind 数据库中的 Customers 表的 ContactName 列创建 DEFAULT 约束，当 INSERT 语句中此列的值没有提供的时候，自动使用“UNKNOWN”作为它的值,4.4.1 DEFAULT 约束,USE Northwind ALTER TABLE dbo.Customers ADD CONSTRAINT DF_contactname DEFAULT UNKNOWN FOR ContactName,CHECK 约束,限制输入到指定列的值只能为某些特定值 语法： CONSTRAINT 约束名 CHECK (逻辑表达式) 两种强制域完整性的方法：CHECK 约束和规则 CHECK 约束定义了一个表达式，若数据修改语句使得表达式值为 FALSE 的话，将拒绝语句执行 规则的功能和 CHECK 约束基本相同，除了语法不同，能力稍弱。规则是为了向下兼容而保留的 规则可定义一次，然后对多个列分别绑定；而 CHECK 约束则需要对每个列定义。但 CHECK 约束的功能略强一些（例如引用同行中其他列的值和调用系统函数等）,4.4.2 CHECK 约束,CHECK 约束（续）,应用 CHECK 约束的注意事项 在每次执行 INSERT 或者 UPDATE 语句的时候校验数据值 可以引用同表中的其他列 不能应用于数据类型为 rowversion 的列 不能包含子查询 可通过执行 DBCC CHECKCONSTRAINTS 语句来返回违反了约束的列 列级 CHECK 约束可省略名字，让系统自动生成 CHECK 约束可使用正则表达式 表达式可以用 AND 以及 OR 连接以表示复杂逻辑 表级 CHECK 约束可引用同行中的多列，但不能跨行或跨表 可用 CHECK 约束来防止输入 NULL 值，例如 CHECK (输入值 IS NOT NULL) NULL 值可能使表达式值为未知，这时数据修改语句仍可执行 CHECK 约束中可使用系统函数,4.4.2 CHECK 约束,CHECK 约束（续）,CHECK 约束示例 为 Employees 表中的 BrithDate 增加 CHECK 约束，使出生日期处于可接受的日期范围内,4.4.2 CHECK 约束,USE Northwind ALTER TABLE dbo.Employees ADD CONSTRAINT CK_birthdate CHECK (BirthDate 01-01-1900 AND BirthDate getdate(),PRIMARY KEY 约束,PRIMARY KEY 约束在表中定义了一个惟一标识每一列的主键 语法：CONSTRAINT 约束名 PRIMARY KEY CLUSTERED | NONCLUSTERED (列,.n) 应用 PRIMARY KEY 约束的注意事项 每张表只能有一个 PRIMARY KEY 约束 输入的值必须是惟一的 不允许空值 将在指定列上创建惟一索引,4.4.3 PRIMARY KEY 约束,PRIMARY KEY 约束（续）,PRIMARY KEY 约束示例 在 Customers 表上创建 PRIMARY KEY 约束，指明表的主键值是 CustomerID，并且创建非聚集索引以强制约束,4.4.3 PRIMARY KEY 约束,USE Northwind ALTER TABLE dbo.Customers ADD CONSTRAINT PK_Customers PRIMARY KEY NONCLUSTERED (CustomerID),UNIQUE 约束,UNIQUE 约束指明列中的任意两行不能有相同的值 语法：CONSTRAINT 约束名 UNIQUE CLUSTERED | NONCLUSTERED (列,.n ) 应用 UNIQUE 约束的注意事项 允许一个空值 在一个表上允许多个 UNIQUE 约束 可在一个或者多个列上定义 是通过一个惟一索引强制约束的,4.4.4 UNIQUE 约束,UNIQUE 约束（续）,PRIMARY KEY 和 UNIQUE 约束 声明 PRIMARY KEY 或 UNIQUE 约束的结果只是自动创建了一个指定列上的惟一索引，通过惟一索引来确保值的惟一性 可空性 PRIMARY KEY 的各个列必须声明为 NOT NULL，而 UNIQUE 的各个列可以声明为允许 NULL 值 在惟一索引中，认为所有的 NULL 值是相等的 索引的属性 PRIMARY KEY 约束所创建的惟一索引默认为 CLUSTERED，除非表中另外一列已经声明为 CLUSTERED UNIQUE 约束所创建的惟一索引默认为 NONCLUSTERED 选择键 保持键的长度尽可能短，必要时可另外创建一个代替键 不要使用 float 或 real 数据类型的列作为主键,4.4.4 UNIQUE 约束,UNIQUE 约束（续）,UNIQUE 约束示例 在 Suppliers 表的公司名列上创建 UNIQUE 约束,4.4.4 UNIQUE 约束,USE Northwind ALTER TABLE dbo.Suppliers ADD CONSTRAINT U_CompanyName UNIQUE NONCLUSTERED (CompanyName),FOREIGN KEY 约束,FOREIGN KEY 约束：定义到同表或其他表中具有 PRIMARY KEY 或者 UNIQUE 约束的列的引用 语法：CONSTRAINT 约束名FOREIGN KEY(列,n) REFERENCES 引用表 (引用列 ,n) 具有 FOREIGN KEY 约束的列的取值范围只能是被引用的列的列值,4.4.5 FOREIGN KEY 约束,FOREIGN KEY 约束（续）,应用 FOREIGN KEY 约束的注意事项 提供了单列或多列的引用完整性。FOREIGN KEY 子句中指定的列的个数和数据类型必须和 REFERENCES 子句中指定的列的个数和数据类型匹配 并不自动创建索引 修改数据的时候，用户必须在被 FOREIGN KEY 约束引用的表上具有 SELECT 或 REFERENCES 权限 若引用的是同表中的列，那么可只用 REFERENCES 子句而省略 FOREIGN KEY 子句,4.4.5 FOREIGN KEY 约束,FOREIGN KEY 约束（续）,FOREIGN KEY 约束的示例 使用 FOREIGN KEY 约束，确保 Orders 表中的客户标识与 Customers 表中的有效的客户标识相关联,4.4.5 FOREIGN KEY 约束,USE Northwind ALTER TABLE dbo.Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES dbo.Customers(CustomerID),级联引用完整性,4.4.6 级联引用完整性,FOREIGN KEY 约束包含一个 CASCADE 选项，允许对一个定义了 UNIQUE 或者 PRIMARY KEY 约束的列的值的修改自动传播到引用它的外键上，这个动作称为级联引用完整性 语法：CONSTRAINT 约束名 FOREIGN KEY (列,n) REFERENCES 引用表 (引用列 ,n). ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NO ACTION：任何企图删除或者更新被其他表的外键所引用的键都将引发一个错误，对数据的改变会被回滚。NO ACTION 是默认值 CASCADE：若父表中的行变化了，则引用表中相应的行也自动变化,级联引用完整性（续）,4.4.6 级联引用完整性,应用 CASCADE 选项的注意事项 可在多个具有引用关系的表之间组合 CASCADE 和 NO ACTION 选项。若 SQL Server 遇到 NO ACTION，则中断并回滚所有相关的 CASCADE 动作 CASCADE 选项不能对定义为 rowversion 数据类型的外键或主键列指定,第4章 实现数据完整性,数据完整性的类型 强制数据完整性 定义约束 约束的类型 禁用约束 使用默认值和规则 决定使用何种强制方法 推荐操作,禁用约束,禁用现有数据上的约束检查 在加载新数据时禁用约束检查,4.5 禁用约束,禁用现有数据上的约束检查,当在表上添加约束的时候，我们可以禁用对已有数据的约束检查 语法：ALTER TABLE 表名 WITH CHECKWITH NOCHECK ADD CONSTRAINT 约束名 FOREIGN KEY (column,n) REFERENCES 引用表 (引用列 ,n) CHECK (搜索条件),4.5.1 禁用现有数据上的约束检查,禁用现有数据上的约束检查（续）,对已有数据禁用约束检查的注意事项 只能禁用 CHECK 和 FOREIGN KEY 约束 当为一个已有数据的表添加 CHECK 或 FOREIGN KEY 约束的时候，使用 WITH NOCHECK 选项来禁用对已有数据的约束检查 当现有数据不再变化的时候，使用 WITH NOCHECK 选项。若数据被更新，则它的新值必须符合 CHECK 约束 确定禁用约束检查是恰当的。在决定添加约束前，可修改现有数据,4.5.1 禁用现有数据上的约束检查,在加载新数据时禁用约束检查,为了避免约束检查的开销，有时候可能希望禁用约束 已经确保数据符合约束 数据并不符合约束，但稍后可以更改其值并重用约束 语法：ALTER TABLE 表名 CHECK|NOCHECK CONSTRAINT ALL|约束名,.n 查看约束是启用还是禁用的状态 系统存储过程 sp_help 系统函数 OBJECTPROPERTY 的 CnstIsDisabled 属性,4.5.2 在加载新数据时禁用约束检查,第4章 实现数据完整性,数据完整性的类型 强制数据完整性 定义约束 约束的类型 禁用约束 使用默认值和规则 决定使用何种强制方法 推荐操作,使用默认值和规则,默认值和规则是可以绑定到一个或多个列或用户定义数据类型的对象，使得可以一次定义，多次使用。但它们不是 ANSI 兼容的。 默认值：若插入数据的时候没有指定某列的值，则此列绑定的默认值将提供一个默认值。 语法：创建：CREATE DEFAULT 默认值 AS 常量表达式 删除：DROP DEFAULT 默认值 ,.n 绑定一个默认： sp_bindefault 解除默认值绑定：sp_unbindefault 创建默认值的注意事项 列的默认值必须符合绑定到此列上的任何规则 列的默认值必须符合此列上的任何 CHECK 约束 不能为已有默认绑定的列或用户定义数据类型创建DEFAULT 约束,4.6 使用默认值和规则,使用默认和规则（续）,规则：规则指定了能插入列的可接受的值。它确保数据在指定值域内，匹配某个模式，或者匹配指定列表中的项。 语法：创建：CREATE