第数据完整性学习教案

1、会计学1第第 数据完整性数据完整性第一页，共76页。第1页/共75页第二页，共76页。数据完整性的基本概念1约束(yush)2默认值3规则(guz)4标识(biozh)列5自定义数据类型6第2页/共75页第三页，共76页。第3页/共75页第四页，共76页。Employees表表编号编号姓名姓名性别性别部门部门电话电话地址地址设置设置(shzh)PRIMARY KEY约束约束第4页/共75页第五页，共76页。XSDA表表学号学号姓名姓名性别性别系名系名总学分总学分定义定义(dngy)0-100之间（之间（CHECK约束）约束）第5页/共75页第六页，共76页。第6页/共75页第七页，共76页。E

2、mployees表表员工编号员工编号姓名姓名性别性别职务职务工资工资Departments表表部门编号部门编号部门名称部门名称部门编号部门编号FOREIGN KEY约束约束(yush)第7页/共75页第八页，共76页。第8页/共75页第九页，共76页。实体完整性可以通过索引、PRIMARY KEY约束、UNIQUE 约束或 IDENTITY 属性(shxng)来实现。下面主要讲述 PRIMARY KEY 约束和 UNIQUE 约束的使用。第9页/共75页第十页，共76页。Server自动为主键列创建惟一索引，以确保数据的惟一性。第10页/共75页第十一页，共76页。第11页/共75页第十二页，

3、共76页。Employees表表编号编号姓名姓名性别性别部门部门电话电话地址地址设置设置(shzh)PRIMARY KEY约束约束第12页/共75页第十三页，共76页。第13页/共75页第十四页，共76页。第14页/共75页第十五页，共76页。CREATE TABLE kc(课程(kchng)号 CHAR(4) NOT NULL CONSTRAINT pk_kch PRIMARY KEY , 课程(kchng)名 CHAR(16) NOT NULL, 学分 SMALLINT , 学时数 SMALLINT )第15页/共75页第十六页，共76页。2)通过修改表创建PRIMARY KEY约束(yu

4、sh)3)删除PRIMARY KEY约束(yush)ALTER TABLE cjADD CONSTRAINT pk_xhkch PRIMARY KEY (学号)ALTER TABLE cjDROP CONSTRAINT pk_xhkch第16页/共75页第十七页，共76页。第17页/共75页第十八页，共76页。Employees表表编号编号姓名姓名性别性别部门部门电话电话地址地址设置设置(shzh)UNIQUE约束约束第18页/共75页第十九页，共76页。 UNIQUE UNIQUE与与PRIMARY KEYPRIMARY KEY的差异的差异UNIQUEUNIQUE和和PRIMARY KEYP

5、RIMARY KEY有些类似，有些类似，为了避免混淆为了避免混淆(hnxio)(hnxio)在此列出二者之间的差异：在此列出二者之间的差异： UNIQUE UNIQUE字段允许输入字段允许输入NULLNULL值，值， 但但PRIMARY KEYPRIMARY KEY字段不允许。字段不允许。 一个数据表中可以定义多个一个数据表中可以定义多个UNIQUEUNIQUE条件条件 约束，但只能定义一个约束，但只能定义一个PRIMARY KEYPRIMARY KEY条件条件 约束约束第19页/共75页第二十页，共76页。第20页/共75页第二十一页，共76页。第21页/共75页第二十二页，共76页。第22

6、页/共75页第二十三页，共76页。CREATE TABLE kc_new(课程(kchng)号 CHAR(4) NOT NULL CONSTRAINT pk_kch1 PRIMARY KEY , 课程(kchng)名 CHAR(16) NOT NULL CONSTRAINT ix_kcm1 UNIQUE, 学分 SMALLINT , 学时数 SMALLINT )第23页/共75页第二十四页，共76页。2)通过修改表创建(chungjin)UNIQUE约束3)删除UNIQUE约束ALTER TABLE kcADD CONSTRAINT ix_kcm UNIQUE (课程(kchng)名)ALTE

7、R TABLE kcDROP CONSTRAINT ix_kcm 第24页/共75页第二十五页，共76页。SQL Server提供了多种方法实现(shxin)不同类型的数据完整性，以保证数据的正确性、相容性和一致性。通过定义 CHECK约束、规则、默认值约束、默认值对象等可以实现(shxin)域完整性。第25页/共75页第二十六页，共76页。第26页/共75页第二十七页，共76页。第27页/共75页第二十八页，共76页。第28页/共75页第二十九页，共76页。第29页/共75页第三十页，共76页。USE xsglCREATE TABLE XSXX(学号学号 char(6), 姓名姓名 char

8、(8),性别性别 char(2) CHECK(性别性别 in (男男,女女),入学入学(r xu)日期日期 datetime)GO第30页/共75页第三十一页，共76页。ALTER TABLE 表名表名 WITH CHECKWITH NOCHECKADD CONSTRAINT 约束约束(yush)名名 CHECK 约束约束(yush)表达式表达式默认设置。表示(biosh)CHECK约束同时作用于已有数据和新数据表示CHECK约束仅作用于新数据，已有数据维持不变第31页/共75页第三十二页，共76页。USE SalesGOALTER TABLE EmployeesADD CONSTRAINT

9、CK_EmployeesNO CHECK (编号(bin ho) like 0-90-90-90-9)GO第32页/共75页第三十三页，共76页。ALTER TABLE 表名表名 DROP CONSTRAINT 约束约束(yush)名名USE xsglALTER TABLE xsDROP CONSTRAINT ck_dhGO第33页/共75页第三十四页，共76页。USE SalesALTER TABLE EmployeesADD CONSTRAINT CK_EmployeesDepCHECK (部门部门(bmn)=财务部财务部 or 部门部门(bmn)=库存部库存部 or 部部门门(bmn)=

10、销售部销售部 or 部门部门(bmn)=采购部采购部)第34页/共75页第三十五页，共76页。CHECK约束比规则更简明，它可以在建表时由CREATE TABLE语句(yj)将其作为表的一部分进行指定，而规则需要单独创建，然后绑定到列上。不需要时可以先解除绑定，然后再删除规则。第35页/共75页第三十六页，共76页。第36页/共75页第三十七页，共76页。CREATE RULE 新规则新规则(guz)名名 AS 规则规则(guz)的条件表达式的条件表达式sp_bindrule 规则规则(guz)名称名称, 表名表名.字段名字段名第37页/共75页第三十八页，共76页。例5.19 创建(chun

11、gjin)名为“总学分”的规则，要求其取值范围在0-50之间USE xsglGOCREATE RULE 总学分总学分 AS score =0 and score=50GOEXEC sp_bindrule 总学分总学分 , xsxx.总学分总学分第38页/共75页第三十九页，共76页。EXEC sp_unbindrule 表名表名.字段名字段名DROP RULE 规则规则(guz)名称名称,n第39页/共75页第四十页，共76页。例5.21：解除总学分列(fn li)与规则总学分之间的绑定关系例：删除总学分规则USE xsglEXEC sp_unbindrule xsxx.总学分总学分DROP

12、RULE 总学分总学分第40页/共75页第四十一页，共76页。第41页/共75页第四十二页，共76页。默认值对象是一种数据库对象，它与默认值对象是一种数据库对象，它与DEFAULT（默认值约束）的作用相同，（默认值约束）的作用相同，也是当向表中输入记录时，没有为某列提供输入值，如果该列被绑定了默认对象，也是当向表中输入记录时，没有为某列提供输入值，如果该列被绑定了默认对象，系统会自动将其值赋给该列。系统会自动将其值赋给该列。 与与DEFAULT约束不同约束不同(b tn)的是默认对象的定义独立于表，其定义一次就可的是默认对象的定义独立于表，其定义一次就可以被多次应用于任意表中的一列或多列，也可

13、以应用于用户定义的数据类型。以被多次应用于任意表中的一列或多列，也可以应用于用户定义的数据类型。 第42页/共75页第四十三页，共76页。（1）默认值约束的定义）默认值约束的定义(dngy)及删除及删除 在管理工具中实现默认值约束的定义在管理工具中实现默认值约束的定义(dngy)以及删以及删除除第43页/共75页第四十四页，共76页。 在SQL语句(yj)中定义及删除USE XSCREATE TABLE XSDA(学号学号char(6) not null, 姓名姓名(xngmng)char(6) not null,专业名专业名 char(10) null,性别性别 char(2) not nu

14、ll default 男男)GO第44页/共75页第四十五页，共76页。例:在修改表时定义(dngy)一个字段的默认值约束。USE XSALTER TABLE XSDAADD 政治面貌政治面貌 char(4) not null constraint zzmmdlt default 团员团员(tunyun) WITH VALUESGo若添加(tin ji)的新列允许空值，使用WITH VALUES，则为表中各现有行添加(tin ji)默认值，若不使用则每一行为NULL。若添加(tin ji)的新列不允许空值，则不论是否有WITH VALUES，表中各现有行添加(tin ji)的新字段都将取默认值

15、第45页/共75页第四十六页，共76页。n 删除默认值约束（用T-SQL语句实现(shxin)）n例：删除上例中定义的默认值约束。USE XSALTER TABLE XSDADROP CONSTRAINT zzmmdltGO第46页/共75页第四十七页，共76页。（2）默认值约束的定义及删除使用(shyng)T-SQL语句定义和绑定default默认值对象定义：绑定：CREATE DEFAULT 默认值名称默认值名称(mngchng)AS 常量表达式常量表达式sp_bindefault 默认默认(mrn)名称名称,表名表名.字段名字段名第47页/共75页第四十八页，共76页。例：设定默认值对象

16、(duxing)，然后将其绑定到XSDA表中的民族字段，实现将该字段的值默认为汉。USE XSGOCREATE DEFAULT mz_default AS 汉汉GOUSE XS EXEC sp_bindefault mz_default,XSDA.民族民族(mnz)GO第48页/共75页第四十九页，共76页。n 使用T-SQL语句删除(shnch)默认值对象n 解绑默认值nn删除(shnch)默认值n sp_unbindefault 表名表名.字段名字段名DROP DEFAULT default_name,n第49页/共75页第五十页，共76页。例：解除民族(mnz)字段列与默认值对象mz_d

17、efault 之间的 绑定关系，并删除默认值对象mz_defaultEXEC sp_unbindefault XSDA.民族民族(mnz)DROP DEFAULT mz_default第50页/共75页第五十一页，共76页。USE SalesALTER TABLE GoodsADD CONSTRAINT DF_GoodsDate DEFAULT(GETDATE() FOR 进货进货(jn hu)时间时间第51页/共75页第五十二页，共76页。Employees表表员工编号员工编号姓名姓名性别性别职务职务工资工资Departments表表部门编号部门编号部门名称部门名称部门编号部门编号FOREI

18、GN KEY约束约束(yush)子表中的子表中的“部门编号部门编号”列是参照父表中主键列。列是参照父表中主键列。要使要使FOREIGN KEY约束有效约束有效1、两张表中、两张表中“部门编号部门编号”列的数据类型必须相同列的数据类型必须相同(xin tn)。2、子表中的、子表中的“部门编号部门编号”列不能包含父表中的列不能包含父表中的“部门编号部门编号”列不存在的值列不存在的值第52页/共75页第五十三页，共76页。说明：说明：当一个记录添加到子表时，系统依靠参照完整性自动当一个记录添加到子表时，系统依靠参照完整性自动 将定义为外键的列和父表中的主键列作比较。只有将定义为外键的列和父表中的主键

19、列作比较。只有 值在父表的主键列中存在时，才能添加该记录值在父表的主键列中存在时，才能添加该记录如果主表中的主键值更改了，那么在整个数据库中，如果主表中的主键值更改了，那么在整个数据库中，对从表中该主键值的所有引用要进行一致更改。对从表中该主键值的所有引用要进行一致更改。如果要删除主表中的某一记录，应先删除从表中与该如果要删除主表中的某一记录，应先删除从表中与该记录匹配记录匹配(ppi)(ppi)的相关记录。的相关记录。第53页/共75页第五十四页，共76页。第54页/共75页第五十五页，共76页。第55页/共75页第五十六页，共76页。第56页/共75页第五十七页，共76页。第57页/共75

20、页第五十八页，共76页。CREATE TABLE XSDA ( 学号学号 char(6) NOT NULL CONSTRAINT xh_pk PRIMARY KEY , 姓名姓名 char(8) NOT NULL, 性别性别 bit NOT NULL DEFAULT(1), 系名系名 char(10) NOT NULL, 出生日期出生日期 smalldatetime NOT NULL , 民族民族(mnz) char(4) NOT NULL , 总学分总学分 tinyint NOT NULL , 备注备注 text NULL)第58页/共75页第五十九页，共76页。CREATE TABLE X

21、SCJ ( 学号学号 char(6) NOT NULL FOREIGN KEY REFERENCES XSDA(学号学号) , 课程课程(kchng)编号编号 char(3) NOT NULL, 成绩成绩 tinyint)第59页/共75页第六十页，共76页。USE XSALTER TABLE XSCJADD CONSTRAINT kc_foreignFOREIGN KEY(课程课程(kchng)编号编号)REFERENCES KCXX(课程课程(kchng)编号编号)GO第60页/共75页第六十一页，共76页。USE XSALTER TABLE XSCJDROP CONSTRAINT kc_foreignGO第61页/共75页第六十二页，共76页。数据完整性的基本概念1约束(yush)2默认值3规则(guz)4标识(biozh)列5自定义数据类型6第62页/共75页第六十三页，共76页。户更新，不允许为空值。第63页/共75页第六十四页，共76页。第64页/共75页第六十五页，共76页。USE xsglALTER TABLE