第4讲 实现数据完整性

返回
删除约束 ALTER TABLE table_name DROP CONSTRAINT constraint_name
只删除约束，并没有删除表； 当删除表时，在该表上定义的约束将自动取消。
使用约束的注意事项
4.2.3 使用约束的注意事项
可直接在表上创建和删除约束，而不必删除并重建 表
当给一个表添加约束的时候，SQL Server 将检查现有 数据是否违反约束
语法： [CONSTRAINT 约束名] DEFAULT 约束表达式 FOR COLUMN_NAME
创建 DEFAULT 约束的两种方法
创建一个默认（CREATE DEFAULT），然后使用存储过程 sp_bindefault 将默认绑定到一个列
CREATE TABLE 或 ALTER TABLE 时使用 DEFAUTL 约束
PRIMARY KEY 约束在表中定义了一个惟一标识每一 列的主键
语法：[CONSTRAINT 约束名 ] PRIMARY KEY [CLUSTERED | NONCLUSTERED ] { (列[,...n])}
应用 PRIMARY KEY 约束的注意事项
每张表只能有一个 PRIMARY KEY 约束 输入的值必须是惟一的 不允许空值 将在指定列上创建惟一索引
规则可定义一次，然后对多个列分别绑定；而 CHECK 约 束则需要对每个列定义。但 CHECK 约束的功能略强一些 （例如引用同行中其他列的值和调用系统函数等）
CHECK 约束（续）
4.3.2 CHECK 约束
CHECK 约束示例
为 Employees 表中的 BrithDate 增加 CHECK 约束，使出生日期处于可接受的日期范 围内
UNIQUE
确保在非主键列中不输入重复值， 并创建一个索引以增强性能。允许 空值
FOREIGN KEY 定义一列或多列的值与同表或其他 表中主键的值匹配
CHECK
基于同表中其他列的值，指定列中 可接受的数据值
创建约束
4.2.2 创建约束
使用 CREATE TABLE 或者 ALTER TABLE
CREATE TABLE 是在创建表时创建约束 ALTER TABLE 是在一个已有的表上创建约束
例3 titleauthor表包含au_id、title_id、au_ord和 royaltyper四个字段，在au_id和title_id上创建主键。
USE pubs ALTER TABLE titleauthor ADD CONSTRAINT pk_title_author
引用完整性规则 若属性（或属性组）F是基本关系R的外码，它
与基本关系S的主码KS相对应，则对于中每个元组 在上的值必须为：
或者取空值
或者等于S中某个元组的主码值
第4章 实现数据完整性
数据完整性的类型 定义约束 约束的类型 使用默认值和规则
定义约束
决定使用何种约束 创建约束 使用约束的注意事项
CHECK 约束
4.3.2 CHECK 约束
限制输入到指定列的值只能为某些特定值
语法： [CONSTRAINT 约束名] CHECK (逻辑表达式)
两种强制域完整性的方法：CHECK 约束和规则
CHECK 约束定义了一个表达式，若数据修改语句使得表达 式值为 FALSE 的话，将拒绝语句执行
规则的功能和 CHECK 约束基本相同，除了语法不同，能 力稍弱。规则是为了向下兼容而保留的
PRIMARY KEY 约束（续）
4.3.3 PRIMARY KEY 约束
PRIMARY KEY 约束示例
在 Customers 表上创建 PRIMARY KEY 约束，指明表 的主键值是 CustomerID，并且创建非聚集索引以强 制约束
USE Northwind ALTER TABLE dbo.Customers ADD CONSTRAINT PK_Customers
实体（或表）完整性要求表中的所有行都有一个惟一的标识 符，称为主键值
引用完整性
引用完整性确保主键（在被引用表中）和外键（在引用表中 ）之间的关系得到保持
域完整性
1.对数据类型的约束（包括数据的类型、长度、单 位、精度等） 2.对数据格式的约束 例如：规定出生日期的格式为YY.MM.DD 3.对取值范围或取值集合的约束 4.对空值的约束 空值表示未定义或未知的值，与零值和空格不同。 5.修改列定义时的约束 6.修改列值时的约束
ALTER TABLE salers ADD CONSTRAINT check_telephone
CHECK (telephone like’([0-9][0-9][0-9])[0-9][0-9] [0- 9][0-9][0-9][0-9][0-9][0-9]’)
PRIMARY KEY 约束
4.3.3 PRIMARY KEY 约束
第4讲 实现数据完整性
数据完整性的类型 定义约束 约束的类型 使用默认值和规则
数据完整性的类型
4.1 数据完整性的类型
数据完整性
指的是数据库中存储的数据的一致性和准确性 数据完整性的类型：域完整性、实体完整性、引用完整性
域完整性
域（或列）完整性是指对列指定一组有效的值并决定是否可 为Baidu Nhomakorabea值
实体完整性
constraint_type：要创建的约束类型
返回
使用alter table 语句创建约束 ALTER TABLE table_name [ WITH CHECK | WITH NOCHECK ] ADD [ CONSTRAINT constraint_name ] constraint_type FOR column_name
可以添加约束到已有数据的表上
可添加单列或多列约束
若约束应用于单列，称为列级约束 若约束引用了多列，称为表级约束，即使它并没有引用表
中的所有列
下一页
CREATE TABLE table_name
(column_name data_type[[CONSTRAINT constraint_name]constraint_type][,…n]) constraint_name:要创建约束的名字， 若省略时，则SQL serer会自动为约束 提供一个名字
建议创建约束的时候指定名称，否则系统将为约束 自动产生一个复杂的名称
名称必须惟一，且符合 SQL Server 标识符的规则 查看约束的信息
系统存储过程：sp_helpconstraint、sp_help
系统表：syscomments、sysreferences、sysconstraints
第4章 实现数据完整性
数据完整性的类型 定义约束 约束的类型 使用默认值和规则
约束的类型
DEFAULT 约束 CHECK 约束 PRIMARY KEY 约束 UNIQUE 约束 FOREIGN KEY 约束 级联引用完整性
4.3 约束的类型
DEFAULT 约束
4.3.1 DEFAULT 约束
如果一个列的值在 INSERT 语句中没有指定，DEFAULT 约束将自 动输入一个值，可以是预先指定的常量、NULL 或者一个系统函 数运行时的值
4.2 定义约束
决定使用何种约束
4.2.1 决定使用何种约束
完整性类型 域
实体
引用
约束类型
描述
DEFAULT
如果在INSERT语句中未显式提供 值，则指定为列提供的值
CHECK
指定列中可接受的数据值
REFERENTIAL 基于其他表中的列的值，指定可接 受的用于更新的数据值
PRIMARY KEY 惟一标识每一列，确保用户没有输 入重复的值。同时创建一个索引以 增强性能。不允许空值
)
例2 修改学生表S，在Scity字段创建一个CHECK约束 ，以限制只能输入有效的城市。
对现有数据不进行检查
ALTER TABLE s WITH NOCHECK ADD CONSTRAINT check_city
CHECK(Scity IN(‘北京’,’上海’,’天津’,’重庆 ’))
例3 修改Salers表，在Telephone字段创建一个 CHECK约束，使得该字段的值的格式为([0-9][0-9][09])[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]。
USE Northwind ALTER TABLE dbo.Employees ADD CONSTRAINT CK_birthdate CHECK (BirthDate > '01-01-1900' AND BirthDate < getdate())
例1 创建学生表S，包含Sid（学号）、Sname（姓名）、Sage（ 年龄）以及Scity（城市）四个字段，并在Sage字段创建一个 CHECK约束，使得Sage的值在18—30岁之间。
返回
实体完整性规则
若属性A是基本关系R的主属性，则属性A不能取空 值。 例如：在关系“学生（学号，姓名，性别，出生日 期，联系方式）”中，“学号”属性为主码，则学 号不能取空值。
返回
引用完整性
例1 学生实体和专业实体可以用下面的关系表示， 其中主码用下划线标识：
学生（学号，姓名，性别，专业号，出生日期） 专业（专业号，专业名） 例2 学生，课程，学生与课程之间的多对多联系可以 用如下三个关系表示： 学生（学号，姓名，性别，专业号，出生日期） 课程（课程号，课程名，学分） 选修（学号，课程号，成绩） 例3 学生（学号，姓名，性别，专业号，年龄，班长）
例 ：修改orders表，在orderdate字段创建default约 束，将当前日期设为默认值，当未给orders表的订 货日期提供值时，取当前日期插入。
ALTER TABLE orders ADD CONSTRAINT default_date DEFAULT getdate()
FOR orderdate
CREATE TABLE s
CHECK(sage
(sid int primary key,
BETWEEN 18 AND 30)
sname char(20),
sage int CONSTRAINT check_age CHECK (sage>=18 and
sage <=30),
scity char(10)
PRIMARY KEY NONCLUSTERED (CustomerID)
例1 创建Orders表，包括OrderID、CustomerID、 SaleID和OrderDate四个字段，其中OrderID字段设为 主键。
Orderid int primary key
CREATE TABLE orders (orderid int constraint pk_orderid PRIMARY KEY , Customerid char(3), Saleid char(3), Orderdate datetime )
例2 已有的salers表，包含saleid、salename、sex等 字段，将该表中saleid字段设为主键。
ALTER TABLE salers ADD CONSTRAINT pk_saleid PRIMARY KEY (saleid)
注意：即使在创建主键约束时带有with nocheck选项 ，系统也要对现存数据进行检查。若现有数据在该 列上出现重复或空值，SQL Server会提示错误信息 ，并拒绝执行创建主键约束操作。
应用 DEFAULT 约束的注意事项
DEFAULT 约束创建时将检查表中的现存数据 为具有 PRIMARY KEY 或 UNIQUE 约束的列指定默认值是没有意义的 每列只能定义一个 DEFAULT 约束 常量值外面可以加或者不加括号，字符或者日期常量必须加上单引
号或双引号
DEFAULT 约束（续）
4.3.1 DEFAULT 约束
DEFAULT 约束示例
为 Northwind 数据库中的 Customers 表的 ContactName 列创建 DEFAULT 约束，当 INSERT 语句中此列的值没有提供的时候，自动使用 “UNKNOWN”作为它的值
USE Northwind ALTER TABLE dbo.Customers ADD CONSTRAINT DF_contactname DEFAULT 'UNKNOWN' FOR ContactName