第5章 数据完整性2

1、第5章 数据完整性（二）5.4.1 主键约束5.4.2 外键约束5.4.3 惟一性约束5.4.4 检查约束5.4.5 默认约束5.4 使用约束实施数据完整性8.4 8.4 使用约束实施数据完整性使用约束实施数据完整性5.4.1 主键约束 约束(Constraint)是SQL Server提供的自动保持数据库完整性的一种机制，它定义了可输入表或表的单个列中的数据的限制条件。使用约束优先于使用触发器、规则和默认值。 约束独立于表结构，作为数据库定义部分在CREATE TABLE语句中声明，可以在不改变表结构的基础上，通过ALTER TABLE语句添加或删除。当表被删除时，表所带的所有约束定义也随之

2、被删除。8.4.1 8.4.1 主键约束主键约束在SQL Server中有6种约束 主键约束 外键约束 惟一性约束 检查约束 默认约束 非空值约束其中非空值约束已在第5章中做了详细介绍，本节介绍其他5种约束。8.4.1 8.4.1 主键约束主键约束主键 表的一列或几列的组合的值在表中惟一地指定一行记录，这样的一列或多列称为表的主键（Primary Key，PK），通过它可强制表的实体完整性。 主键不允许为空值，且不同两行的键值不能相同。表中可以有不止一个键惟一标识行，每个键都称为侯选键，只可以选一个侯选键作为表的主键，其他侯选键称作备用键。 如果一个表的主键由单列组成，则该主键约束可以定义为该

3、列的列约束。如果主键由两个以上的列组成，则该主键约束必须定义为表约束。8.4.1 8.4.1 主键约束主键约束 定义列级主键约束的语法格式如下：CONSTRAINT constraint_name PRIMARY KEY CLUSTERED | NONCLUSTERED 定义表级主键约束的语法格式如下：CONSTRAINT constraint_name PRIMARY KEY CLUSTERED | NONCLUSTERED (column_name , n )8.4.1 8.4.1 主键约束主键约束例5-17 在Sales数据库中创建customer表，并声明主键约束。CREATE TAB

4、LE Sales.dbo.customer( customer_id bigint NOT NULL IDENTITY(0,1) PRIMARY KEY, customer_name varchar(50) NOT NULL, linkman_name char(8), address varchar(50), telephone char(12) NOT NULL )8.4.1 8.4.1 主键约束主键约束例5-17 若定义customer_id列为非聚集主键约束，并指定约束名为PK_customer.CREATE TABLE Sales.dbo.customer( customer_id

5、bigint NOT NULL IDENTITY(0,1) CONSTRAINT PK_customerPRIMARY KEYNONCLUESTERD, customer_name varchar(50) NOT NULL, linkman_name char(8), address varchar(50), telephone char(12) NOT NULL )8.4.1 8.4.1 主键约束主键约束例5-18 创建一个产品信息表goods1，将产品编号goods_id列声明为主键。CREATE TABLE goods1( goods_id char(6) NOT NULL, goods

6、_name varchar(50) NOT NULL, classification_id char(6) NOT NULL, unit_price money NOT NULL, stock_quantity float NOT NULL, order_quantity float NULL , CONSTRAINT pk_p_id PRIMARY KEY (goods_id)ON PRIMARY8.4.1 8.4.1 主键约束主键约束例5-19 根据商品销售的时间和商品类别来确定销售的商品的数量。CREATE TABLE g_order( good_type int, order_time

7、 datetime, order_num int, CONSTRAINT g_o_key PRIMARY KEY (good_type, order_time)例: 1) 复制学生表的信息生成新表xs. 2) 为xs表添加主键约束,该主键约束由学号单列组成,约束 名为pk_xh. 3) 修改xs表的主键约束,该主键约束由学号和姓名两列 组成,约束名为pk_xh_xm.1) SELECT * INTO xs FROM student_info2) ALTER TABLE xs ADD CONSTRAINT pk_xh PRIMARY KEY (学号) GO EXEC sp_helpindex x

8、s3) ALTER TABLE xs DROP CONSTRAINT pk_xh GO ALTER TABLE xs ADD CONSTRAINT pk_xh_xm PRIMARY KEY(学号,姓名)习题习题1)1) 建立建立employeeemployee表表, ,包括字段有包括字段有 职工号职工号 char(5),char(5),姓名姓名 varchar(8),varchar(8),出生日期出生日期 datetime,datetime, 要求在建立的同时要求在建立的同时, ,设定职工号为主键设定职工号为主键, ,主键名取默认主键名取默认. .CREATE TABLECREATE TABL

9、E employee employee( (职工号职工号 charchar(4) (4) PRIMARY KEYPRIMARY KEY, , 姓名姓名 varcharvarchar(8),(8), 出生日期出生日期 datetimedatetime) )2)2) 建立建立order1order1表表, ,包括字段有包括字段有订单号订单号 char(8),char(8),订货单位订货单位 varchar(10),varchar(10),订货日期订货日期 datetimedatetime 要求在建立的同时要求在建立的同时, ,设定订单号和订货订货单位为主键设定订单号和订货订货单位为主键, ,主主

10、键名为键名为PK_dh_hh.PK_dh_hh.CREATE TABLECREATE TABLE order1 order1( ( 订单号订单号 charchar(8),(8), 订货单位订货单位 varcharvarchar(10),(10), 订货日期订货日期 datetimedatetime, , CONSTRAINTCONSTRAINT PK_dh_hh PK_dh_hh PRIMARY KEYPRIMARY KEY ( (订单号订单号, ,订货单位订货单位) ) )3)3) 修改修改order1order1表的主键约束表的主键约束, ,该主键约束由订单号组该主键约束由订单号组成成,

11、,主键约束名为主键约束名为PK_dh.PK_dh.ALTER TABLEALTER TABLE order1 order1DROP CONSTRAINTDROP CONSTRAINT Pk_dh_hh Pk_dh_hhGOGOALTER TABLE ALTER TABLE order1order1ADD CONSTRAINT ADD CONSTRAINT PK_dhPK_dhPRIMARY KEY PRIMARY KEY ( (订单号订单号) )8.4 8.4 使用约束实施数据完整性使用约束实施数据完整性5.4.2 外键约束 外键约束定义了表与表之间的关系。通过将一个表中一列或多列添加到另一个

12、表中，创建两个表之间的连接，这个列就成为第二个表的外键(Foreign Key，FK)，即外键是用于建立和加强两个表数据之间的连接的一列或多列，通过它可以实现强制参照完整性约束。8.4.2 8.4.2 外键约束外键约束例如，Sales数据库中的employee、sell_order、goods这3个表之间存在以下逻辑联系：sell_order (销售订单)表中employee_id(员工编号)列的值必须是employee表employee_id列中的某一个值，因为签订销售订单的人必须是当前公司员工；而sell_order表中goods_id(货物编号)列的值必须是goods (货物)表的goo

13、ds_id列中的某一个值，因为销售订单上售出的只能是已知货物。因此，在sell_order表上应建立两个外键约束FK_sell_order_employee和FK_sell_order_goods来限制sell_order表employee_id列和goods_id列的值必须分别来自employee表的employee_id列及goods表的goods_id列。8.4.2 8.4.2 外键约束外键约束级联操作 SQL Server提供了两种级联操作以保证数据完整性：(1) 级联删除确定当主键表中某行被删除时，外键表中所有相关行将被删除。(2) 级联修改确定当主键表中某行的键值被修改时，外键表中

14、所有相关行的该外键值也将被自动修改为新值。8.4.2 8.4.2 外键约束外键约束外键约束与主键约束相同，也分为表约束与列约束。定义表级外键约束的语法格式如下：CONSTRAINT constraint_nameFOREIGN KEY (column_name , n )REFERENCES ref_table (ref_column , n ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION 定义列级外键约束的语法格式如下：CONSTRAINT constraint_nameFOR

15、EIGN KEYREFERENCES ref_table NOT FOR REPLICATION 8.4.2 8.4.2 外键约束外键约束例5-20 创建一个订货表sell_order1，与例5-18创建的产品表goods1相关联。CREATE TABLE sell_order1( order_id1 char(6) NOT NULL, goods_id char(6) NOT NULL, employee_id char(4) NOT NULL, customer_id char(4) NOT NULL, transporter_id char(4) NOT NULL, order_num

16、float NULL, discount float NULL, order_date datetime NOT NULL, send_date datetime NULL, arrival_date datetime NULL, cost money NULL, CONSTRAINT pk_order_id PRIMARY KEY (order_id1), FOREIGN KEY (goods_id) REFERENCES goods1(goods_id)例例5-21 创建表创建表sell_order2，并为，并为goods_id、employee_id、custom_id三三列定义外键约束

17、。列定义外键约束。CREATE TABLE sell_order2( order_id1 char(6) PRIMARY KEY, goods_id char(6) NOT NULL REFERENCES Goods1 ON DELETE NO ACTION ON UPDATE CASCADE, employee_id char(4) NOT NULL REFERENCES employee ON UPDATE CASCADE, customer_id char(4) NOT NULL, transporter_id char(4) NOT NULL, order_num float, dis

18、count float, order_date datetime NOT NULL, send_date datetime, arrival_date datetime, cost money, CONSTRAINT FK_customer_id FOREIGN KEY (customer_id) REFERENCES customer(customer_id)8.4.2 8.4.2 外键约束外键约束 employee_id列的外键应当定义如下：CONSTRAINT FK_sell_order_employee /* 可省略 */ FOREIGN KEY (department_id , em

19、ployee_id) REFERENCES employee(department_id , employee_id)例如，如果employee表的主键是由department_id和employee_id组成的复合主键，则sell_order表中应增加相同类型的department_id列定义例例: :为选课表添加外键约束为选课表添加外键约束, ,约束名为约束名为FK_kcbh,FK_kcbh,使之与课程表建使之与课程表建 立级联删除和级联更新操作立级联删除和级联更新操作. .ALTER TABLE 选课表ADD CONSTRAINT FK_kcbh FOREIGN KEY (课程编号) R

20、EFERENCES 课程表(课程编号)ON DELETE CASCADEON UPDATE CASCADEUPDATE 课程表SET 课程编号=1111WHERE 课程编号=0001DELETE 课程表WHERE 课程编号=1111ALTER TABLE 选课表DROP CONSTRAINT FK_kcbh例例: :为选课表添加外键约束为选课表添加外键约束, ,约束名为约束名为FK_xh,FK_xh,使之与学生表建使之与学生表建 立级联删除和级联更新操作立级联删除和级联更新操作. .ALTER TABLEALTER TABLE 选课表选课表ADD CONSTRAINTADD CONSTRAIN

21、T FK_xh FK_xh FOREIGN KEYFOREIGN KEY ( (学号学号) ) REFERENCESREFERENCES 学生表学生表( (学号学号) )ON DELETE CASCADEON DELETE CASCADEON UPDATE CASCADEON UPDATE CASCADEALTER TABLEALTER TABLE 选课表选课表DROP CONSTRAINTDROP CONSTRAINT FK_xhFK_xh8.4 8.4 使用约束实施数据完整性使用约束实施数据完整性5.4.3 惟一性约束惟一性(Unique)约束指定一个或多个列的组合的值具有惟一性，以防止在

22、列中输入重复的值，为表中的一列或者多列提供实体完整性。惟一性约束指定的列可以有NULL属性。主键也强制执行惟一性，但主键不允许空值，故主键约束强度大于惟一约束。因此主键列不能再设定惟一性约束。8.4.3 8.4.3 惟一性约束惟一性约束 定义列级惟一性约束的语法格式如下：CONSTRAINT constraint_nameUNIQUE CLUSTERED | NONCLUSTERED 惟一性约束应用于多列时的定义格式：CONSTRAINT constraint_nameUNIQUE CLUSTERED | NONCLUSTERED(column_name , n )8.4.3 8.4.3 惟一

23、性约束惟一性约束例5-22 创建goods2表，使goods_name具有惟一性约束。CREATE TABLE goods2( goods_id char(6) NOT NULL PRIMARY KEY, goods_name varchar(50) NOT NULL CONSTRAINT u_goods_name UNIQUE NONCLUSTERED, classification_id char(6) NOT NULL, unit_price money NOT NULL, stock_quantity float NOT NULL, order_quantity float)8.4.3

24、 8.4.3 惟一性约束惟一性约束CREATE TABLE employees( emp_id char(8), emp_name char(10) , emp_cardid char(18), CONSTRAINT pk_emp_id PRIMARY KEY (emp_id), CONSTRAINT uk_emp_cardid UNIQUE (emp_cardid)例5-23 定义一个员工信息表employees，其中员工的身份证号emp_cardid列具有惟一性。例：例：1 1）建立）建立XSXS表，包括学号表，包括学号 char(2) char(2) 姓名姓名 char(6)char(6

25、)，同时以学，同时以学 号建立唯一性约束，约束名为号建立唯一性约束，约束名为UK_xh.UK_xh. 2 2）为）为XSXS表以姓名建立唯一性约束，约束名为表以姓名建立唯一性约束，约束名为UK_xmUK_xm。 3 3）删除）删除XSXS表中的表中的UK_xhUK_xh的唯一性约束。的唯一性约束。CREATE TABLE XS(学号 char(2) CONSTRAINT UK_xh UNIQUE, 姓名 char(6)ALTER TABLE XS ADD CONSTRAINT UK_xm UNIQUE(姓名)ALTER TABLE XS DROP CONSTRAINT UK_xh8.4 8.4

26、 使用约束实施数据完整性使用约束实施数据完整性5.4.4 检查约束 检查(Check)约束对输入列或整个表中的值设置检查条件，以限制输入值，保证数据库的数据完整性。 当对具有检查约束列进行插入或修改时，SQL Server将用该检查约束的逻辑表达式对新值进行检查，只有满足条件(逻辑表达式返回TRUE)的值才能填入该列，否则报错。可以为每列指定多个CHECK约束。8.4.4 8.4.4 检查约束检查约束定义检查约束的语法格式：CONSTRAINT constraint_nameCHECK NOT FOR REPLICATION(logical_expression)8.4.4 8.4.4 检查约

27、束检查约束例5-24 更改表employee2以添加未验证检查约束。ALTER TABLE employee2 WITH NOCHECK ADD CONSTRAINT CK_Age CHECK (DATEDIFF(year, Birth_Date, Hire_Date)18)防止对现有行进行验证约防止对现有行进行验证约束，允许在存在违反约束束，允许在存在违反约束的值的情况下添加该约束的值的情况下添加该约束8.4.4 8.4.4 检查约束检查约束例5-25 创建一个订货表orders，保证各订单的订货量必须不小于10。CREATE TABLE orders( order_id char(8),

28、p_id char(8), p_name char(10) , quantity smallint CONSTRAINT chk_quantity CHECK (quantity=10), CONSTRAINT pk_orders_id PRIMARY KEY (order_id) )8.4.4 8.4.4 检查约束检查约束CREATE TABLE transporters( transporter_id char(4) NOT NULL, transport_name varchar(50), linkman_name char(8), address varchar(50), teleph

29、one char(12) NOT NULL CHECK(telephone LIKE 01-90-90-9-1-90-90-90-90-90-90-9 OR telephone LIKE 01-90-9-1-90-90-90-90-90-90-90-9)例5-26 创建transporters表并定义检查约束CREATE TABLECREATE TABLE score score( (学号学号 charchar(4),(4), 课号课号 charchar(1),(1), 分数分数 decimaldecimal(5,2) (5,2) CONSTRAINTCONSTRAINT CK_cj CK_cj CHECK CHECK ( (分数分数=0 and =0 and 分数分