SQL SERVER数据完整性及约束

• 按照在数据库中实现数据完整性的作用级 别，可以将其分为对象级与总体级。 • 对象级，作用范围是数据库的某个表对象 ，是在定义表的同时定义的，删除表则失 去作用，如约束。 • 总体级：是作为数据库的对象单独定义的 ，因此单独存在于数据库中，需要时可以 绑定到数据库的表或用户定义的数据类型 中，如默认对象、规则。
• 创建part_sample表时添加主键约束，指明约束 名称 • create table part_sample • ( • part_number int constraint pk_partnumber primary key, • part_name varchar(30), • part_weight decimal(6,2), • part_color varchar(15) • )
• insert into t_user values (1, 'qianxin', 1); --可 以插入 • insert into t_user values (2, 'yiyu', 2); --可以 插入 • insert into t_user values (3, 'dai', 3); --错误 ，无法插入，用户组3不存在，与参照完整 性约束不符
正确与错误的对比
Pro_id OA0001 Pro_name 洗衣粉 Pro_price 27.00 Sto_id STO00001 Pro_id OA0001 Pro_num 300
Pro_id OA0001
Pro_name 洗衣粉
Pro_price 27.00
Sto_id STO00001
Pro_id 0001
• 直接添加主键信息会怎么样?应该如何处理 ？ • alter table student add constraint pk_stuid primary key (stu_id)
• 处理方式为：先修改要定义为主键的列为 非空列，再将其定义为主键 • alter table student alter column stu_id int not null
• 按照数据完整性的实施方法包括：约束、 默认、规则、触发器、存储过程等。
• 约束的用途是限制输入到表中的值的范围 。SQL server根据不同的用途提供了多种约 束，分别是： • Primary key(主键)约束 • Foreign key(外键)约束 • Uniqe(惟一)约束 • Check(核查)约束 • Nullable约束 • Cascade级联引用一致性约束
• 创建part_sample表，包含part_number（PK） 、part_name、part_weight、part_color字段， 不指明主键约束的名称 • create table part_sample • ( • part_number int primary key, • part_name varchar(30), • part_weight decimal(6,2), • part_color varchar(15) • )
• alter table student add constraint pk_stuid primary key (stu_id)
• 创建表authors，表中含有au_id（PK）、 au_name、pseudonym字段，其中pseudonym 字段带有唯一约束，不指明约束名称 • create table authors • ( • au_id varchar(20) primary key, • au_name varchar(20), • pseudonym varchar(20) unique • )
ห้องสมุดไป่ตู้
创建表时添加唯一约束，指明约束名称 create table authors ( au_id varchar(20) primary key, au_name varchar(20), pseudonym varchar(20) constraint UQ_pseudonym unique • ) • • • • • •
Primary 主键约束
• Primary 主键约束利用表中的一列或多列数 据来惟一地标识一行数据。在表中，绝不 请允许有主键相同的两行存在。在受主键 约束的任何一列上都一定要有确定的数据 ，不能输入NULL值代替。为了有效实现数 据的管理，每一张表都应该有自己的主键 ，且只能有一个主键。
• 注意：在同一张表上，也许存在多于一种 列的组合可以惟一地标识一行数据。这任 意一种列的组合 被称为候选键(candidate key)，数据库管理员可以根据实际情况从候 选键中选 出一个(也只能挑选一个)合适的作 为 表的主键。 • 主键不能为空，不能重复
数据完整性基础
• 所谓数据完整性，事实上，是衡量数据库 中数据质量好坏的一种标志，是确保数据 库中数据一致、正确以及符合企业规则的 一种思想，是使无序的数据条理化，确保 正确的数据被存在正确的位置的一种手段 。
数据完整性的特点
• 数据完整性要求的数据具有以下3个特点： • 1、数据的值正确无误。 • 这有两个方面的意思：首先，数据类型必 须正确，其次，数据的值必须处于正确的 范围内。 • 例如：Bithdate(生日)必须取值范围在 1753/1/1~9999/1/1
• 3、引用完整性，指对数据库进行添加、删 除、修改数据时，要维护表间数据一致性 。实现方法：外键约束foreign key、核查约 束check、触发器trigger、存储过程 procedure
• 用户定义的完整性，用于实现用户特殊要 求的数据规则或格式，便如在学生库中， 学生表的出生日期列数据可以规定格式为 ddd-dd-dddd形式(d表示0~9之间的数据)。 实现方法：默认值default、核查约束check 、规则rule等。
• 创建订单表book_order，表中包含orderID（PK）、 bookID（PK）、clientID、quantity、purchase_date字段 • create table book_order • ( • orderID varchar(10), • bookID varchar(5), • clientID varchar(10), • quantity int, • purchase_date datetime, • constraint pk_orderkey primary key (orderID,bookID) • )
• 对于已经创建的表添加主键，将要设置为主键 的字段并未指明是非空字段的处理方法 • 创建student表，表中含有stu_id、name、sex字 段，随后再修改此表，为其添加主键 • create table student • ( • stu_id varchar(10), • name varchar(20), • sex char(2) • )
• • • • • • •
修改表时添加唯一约束，不指明约束名称 create table authors ( au_id varchar(20) primary key, au_name varchar(20), pseudonym varchar(20) )
• alter table authors add unique(pseudonym)
外键约束
• 外键约束主要用来维护两个表之间的一致 性关系。外键的建立主要是通过将一个表 中的主键所在列包含在另一个表中，这些 列就是另一个表的外键。
Pub_id Pub_name city
Title_id title Pub_id type
state price
外键约束
• 创建表Order_part，含有order_number、 part_number、qty_orderde字段，建立一个外键引用 前面定义的表part_sample中的part_number。 • create table order_part • ( • order_number int, • part_number int foreign key references part_sample (part_number) on delete no action, • qty_orderde int • )
• 约束可以分为表级约束和列级约束两种： • 列级约束： • 列级约束是行定义的一部分，只能够应用 在一列上 • 表级约束： • 表级约束的定义独立于列的定义，可以应 用在一个表中的多列上。 • 注意：当需要在一个表中的多列上建立约 束时，只能建立表级的约束
• • • •
要看有关的约束的信息，可以使用 Sp_helpconstraint系统存储过程 语法如下： Sp_helpconstraint table_name
列级约束
• • • • • • • create table users ( u_id int identiyt(1,1) primary key, u_name varchar(50), u_datetime datetime ) 定义了一个主键的约束
表级约束
• • • • • • • create table users ( u_id int, u_name varchar(50), u_datetime datetime constraint u_id primary key(u_id,u_datetime) )
Pro_num 300
数据完整性分类
• 1、实体完整性，要求表中每一条记录（每 一行数据）是唯一的即它必须至少拥有一 个唯一标识以区分不同的数据行。实现方 法有：主键约束primary key、唯一性约束 unique、唯一索引unique index、标识 identity等
• 2、值域完整性 ：限定表中输入数据的数据 类型与取值范围。实现方法：默认值约束 default或默认对象、核查约束check、外键 约束foreign key、规则rule、数据类型非空 性约束not null等。
• • • • • • •
修改表时添加唯一约束，指明约束名称 create table authors ( au_id varchar(20) primary key, au_name varchar(20), pseudonym varchar(20) )
• alter table authors add constraint UQ_pseudonym unique(pseudonym)
• 下面创建用户表，分别以不同的约束方式创建外键 引用关系 • 1、级联(cascade)方式 create table t_user ( • id int not null, • name varchar(30), • groupid int, • primary key (id), • foreign key (groupid) references t_group(id) on delet e cascade on update cascade • );
• 2、数据的存在必须确保同一表格数据之间 的和谐关系。 • 例如：在一个表中，已经存在了分配给某 一个同学的学号为KDE00001，则再也不许 将KDE00001这个学号在一次分配给别的同 学。
• 3、数据的存在必须能确保维护不同表格数 据之间的和谐关系。 • 例如：我们有产品表和库存表两个表，现 在我产品表里有一列是productID这个字段 ，该字段必须与我库存表里的产品ID这个字 段相对应。不请允许出现在产品表中与库 存表中不统一的情况。
注：字段名所指字段必须有主键或唯一性约 束 with nocheck 不检查表中现有数 据 on delete 删除 on update 更新 cascade 级联 no action 不级联
外键示例
• • • • • • • create table t_group ( id int not null, name varchar(30), primary key (id) ) insert into t_group values (1, 'Group1') insert into t_group values (2, 'Group2')