数据完整性在SQL中的实现.

1、数据完整性在SQL中的实施数据完整性问题：数据完整性的含义？问题：数据完整性的含义？ 作用：作用：防止数据库中存在不符合语义的数据，也就是防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据防范对象防止数据库中存在不正确的数据防范对象不合语义的、不正确的数据不合语义的、不正确的数据数据完整性 问题：数据完整性的类型有哪几类？问题：数据完整性的类型有哪几类？实体完整性实体完整性 问题：实体完整性的含义？问题：实体完整性的含义？ 常见的实体完整性机制包括：常见的实体完整性机制包括：主键（主键（Primary Key）；）；惟一码（惟一码（Unique）；）；参照完整性参照完整性 问

2、题：参照完整性指的是什么？问题：参照完整性指的是什么？可以防止数据丢失或者无意义的数据；可以防止数据丢失或者无意义的数据；可以禁止在从表中插入被参照表中不存在的关可以禁止在从表中插入被参照表中不存在的关键字的记录。键字的记录。 参照完整性的常见实现机制包括：参照完整性的常见实现机制包括：外键（外键（Foreign Key）、触发器（）、触发器（Trigger）；）；域完整性域完整性 域完整性要求列（字段）的数据具有正确的数据域完整性要求列（字段）的数据具有正确的数据类型、格式和有效的数值范围。类型、格式和有效的数值范围。 域完整性的常见实现机制包括：域完整性的常见实现机制包括：默认值（默认值（

3、Default）检查（检查（Check）数据类型（数据类型（Data type）规则（规则（Rule）用户自定义完整性用户自定义完整性 用户定义的完整性就是针对某一具体应用的数据必须用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求；可直接由满足的语义要求；可直接由RDBMS提供，而不必由应提供，而不必由应用程序承担；系统将实现数据完整性的要求直接定义用程序承担；系统将实现数据完整性的要求直接定义在表上或列上。在表上或列上。 常见的实现机制包括：常见的实现机制包括：规则（规则（Rule）、创建数据表时的所有约束）、创建数据表时的所有约束（Constraint）、触发器（）、触发器（T

4、rigger）、存储过程）、存储过程（Stored Produre）；在SQL Server 2005中实现数据完整性 SQL Server 2005提供了完善的数据完整性提供了完善的数据完整性机制，主要包括约束、默认值和规则机制，主要包括约束、默认值和规则3类；类；创建及管理约束及约束对象创建及管理约束及约束对象 默认值约束及默认值对象默认值约束及默认值对象创建及管理规则对象创建及管理规则对象创建及管理约束及约束对象创建及管理约束及约束对象 主键主键Primary key约束约束 惟一值惟一值Unique约束约束 外键外键Foreign key约束约束 检查检查Check约束约束 默认默认D

5、efault约束约束定义PRIMARY KEY约束语法结构：语法结构：CREATE TABLE table_name(column_name data_type (NULL| NOT NULL) CONSTRAINT constraint_name PRIMARY KEY | unique, .n 注意注意: 每一个表只能定义一个每一个表只能定义一个PRIMARY KEY约束约束,主键列的输入值必须是主键列的输入值必须是唯一的唯一的,不允许空值不允许空值,该唯一性索引在默认情况下为簇索引该唯一性索引在默认情况下为簇索引,如果表中已如果表中已有簇索引应先删除再建立索引有簇索引应先删除再建立索引.

6、 create table st1 (sno varchar(8) primary key,sname varchar(10)问题：问题：1、在哪种操作下验证？、在哪种操作下验证？2、INSERT数据后观察数据的顺序有什么特点数据后观察数据的顺序有什么特点定义UNIQUE约束 UNIQUE约束约束:指定表中某一个列不能有相同数据指定表中某一个列不能有相同数据的两个行的两个行. 当表中已有主键值当表中已有主键值,还要强调另外列的唯一性时还要强调另外列的唯一性时,用用UNIQUE约束约束.例例:create table st2(number int primary key ,name varcha

7、r(8) null unique)name允许空值允许空值,一个表可以有多个一个表可以有多个UNIQUE 约束约束.ii）通过修改表时同时创建）通过修改表时同时创建primary key约束或约束或unique约束约束 语法格式：语法格式：ALTER TABLE table_nameADD CONSTRAINT constraint_name primary key | unique(column ,.n)参照完整性参照完整性 参照完整性是指在两个表的主键和外键之间数据的完整参照完整性是指在两个表的主键和外键之间数据的完整性，其含义包括：性，其含义包括：参照完整性保证被参照表和参照表之间数据的

8、一致性；参照完整性保证被参照表和参照表之间数据的一致性；可以防止数据丢失或者无意义的数据；可以防止数据丢失或者无意义的数据；可以禁止在从表中插入被参照表中不存在的关键字的可以禁止在从表中插入被参照表中不存在的关键字的记录。记录。 参照完整性的常见实现机制包括：参照完整性的常见实现机制包括：外键（外键（Foreign Key）、触发器（）、触发器（Trigger）；）；定义FOREIGN KEY约束 FOREIGN KEY 约束约束:参考同一个表或者另外参考同一个表或者另外一个表中的一个表中的PRIMARY KEY 或者或者UNIQUE约束。约束。 语法语法: :CONSTRAINT const

9、raint_name FOREIGN KEY (column,n) REFERENCES ref_table (ref_col,n)定义FOREIGN KEY约束Create table s1（sno char(9) primary key，sname）Create table c1（cno char(4) primary key，sname）Create table sc1（sno char(9) foreign key references s1(sno) ,cno char(4) foreign key references c1(cno) ,Grade smallint ）注意：建表的

10、顺序以及如何合理的处理表与表间的数据关系注意：建表的顺序以及如何合理的处理表与表间的数据关系(insert,update,delete时应该注意什么？）时应该注意什么？）外码约束选项说明 ON DELETE CASCADE：级联删除。当删除主表中的记：级联删除。当删除主表中的记录时，如果在子表中有对这些记录的值的引用，则一起录时，如果在子表中有对这些记录的值的引用，则一起删掉；删掉； ON DELETE NO ACTION：限制删除。当删除主表中的：限制删除。当删除主表中的记录时，如果在子表中有对这些记录的值的引用，则拒记录时，如果在子表中有对这些记录的值的引用，则拒绝删除主表中的记录；绝删除

11、主表中的记录； ON UPDATE CASCADE：级联更新。当更新主表中的：级联更新。当更新主表中的有子表引用的列的值时，如果在子表中有对这个列值的有子表引用的列的值时，如果在子表中有对这个列值的引用，则一起更改；引用，则一起更改； ON UPDATE NO ACTION：限制更新。当更新主表中的：限制更新。当更新主表中的有子表引用的列的值时，如果在子表中有对这个列值的有子表引用的列的值时，如果在子表中有对这个列值的引用，则拒绝更改主表中的记录；引用，则拒绝更改主表中的记录；定义FOREIGN KEY约束 CREATE TABLE sc2(sno char(9) not null ,cno

12、char(4) not null,grade int , primary key (sno,cno), foreign key(cno) references c1(cno) on d e l e t e c a s c a d e o n u p d a t e cascade,foreign key (sno) references s1(sno)on delete cascade on update cascade)注意：与上题处理方式的不同？注意：与上题处理方式的不同？定义FOREIGN KEY约束 在在FOREIGN KEY语句中列的数量和数据类型必须和语句中列的数量和数据类型必须和

13、REFERENCES子句中的列的数量和数据类型匹配子句中的列的数量和数据类型匹配. 不像不像PRIMARY KEY 或者或者UNIQUE 约束约束,FOREIGN KEY 约束不能自动创建索引约束不能自动创建索引. 参考同一个表中的列时参考同一个表中的列时,必须只使用必须只使用REFERENCES 子句子句,不能使用不能使用FOREIGN KEY 子句子句. CREATE TABLE suc(number1 char(8) UNIQUE,number2 char(8) REFERENCES suc(number1)注意：注意：number2的数据只能取哪两种？的数据只能取哪两种？ ii）通过修

14、改表定义通过修改表定义Foreign key约束约束 语法格式：语法格式：ALTER TABLE table_nameADD constraint constraint_nameFOREIGN KEY (column,n)REFERNCES ref_table(ref_column ,n) on delete CASCADE | no action on update CASCADE | no action定义check约束i）创建数据表时创建检查）创建数据表时创建检查check约束约束语法格式：CREATE TABLE table_name(column_name data_type (NU

15、LL|NOT NULL) CONSTRAINT check_name CHECK (logical_expression)，n ）例例1：定义：定义student表，并限定表，并限定ssex只允许取只允许取男男或或女女；CREATE TABLE student（ Sno char(9) Primary Key,Sname char(8),Ssex char(2) check （ssex in （男男，女女） ，Sage smallint ，Sdept varchar(20)）定义约束练习：在定义练习：在定义Student中，要求男生的年龄在中，要求男生的年龄在1525岁之间，女生岁之间，女生的

16、年龄在的年龄在1524岁之间；岁之间；CREATE TABLE Student (Sno char(9) Primary Key,Sname char(8),ssex char(2), Sage smallint, Sdept varchar(20),CHECK(SAGE=15 AND ( (SSEX=男 AND SAGE=25) OR (SSEX=女 AND SAGE=24) )定义default约束默认值约束的作用：就是当用户没有为定义为默认值约束的作用：就是当用户没有为定义为default约束的字段输入约束的字段输入数据时数据时, 由由default 约束提供默认的数据。约束提供默认的数

17、据。定义默认值约束语法格式：定义默认值约束语法格式：Create table table_name( column_name datatype not null / null Constraint constraint_name DEFAULT (constraint_expression),n-参数参数n表示可定义多个数据字段表示可定义多个数据字段)-定义列名、数据类型、是否空值及默认值约束定义列名、数据类型、是否空值及默认值约束定义约束 例例create table student(number char(8) primary key , name varchar(8), age int

18、CONSTRAINT aged DEFAULT 20 ,depa varchar(6), sex char(2) not null DEFAULT 女女)注意注意: 该约束只能用于该约束只能用于INSERT 语句语句,一个列只能定义一个一个列只能定义一个DEFAULT约束约束. ii）修改数据表时添加检查）修改数据表时添加检查check约束约束语法格式：语法格式：ALTER TABLE table_nameADD CONSTRAINT check_name - check约束名称约束名称CHECK (logical_expression)-check约束表达式约束表达式举例CREATE TAB

19、LE 工作工作( 工作编号工作编号 char(8) PRIMARY KEY, 最低工资最低工资 int , 最高工资最高工资 int, CHECK ( 最低工资最低工资 = 1000 ), 电话号码电话号码 char(8) NOT NULL UNIQUE )举例CREATE TABLE Student ( Sno char(7) PRIMARY KEY, Sname char(10) NOT NULL, Ssex char(2), Sage tinyint check(sage=20 and sage=0 and grade20 and age=25) alter table student1

20、add constraint default1 default 男 for sex alter table sc1 add constraint f11-key foreign key (sno) references s(sno)在已有的表上创建约束注意：注意： 1、系统在执行时先检查表中原有的数据，如系统在执行时先检查表中原有的数据，如果原有的数据与现在要果原有的数据与现在要 增加的约束矛盾，要么修增加的约束矛盾，要么修改原有数据，要么不能增加现在的约束。改原有数据，要么不能增加现在的约束。 2、 如果要修改如果要修改 一个约束，应该先删除原来的一个约束，应该先删除原来的约束，约束， 命令

21、为：命令为： ALTER TABLE 表名表名 DROP constraint 约束约束名，再重新创建（增加）约束。名，再重新创建（增加）约束。规则RULE对象 规则：对录入数据列中的数据所实施的完整性约束条件，规则：对录入数据列中的数据所实施的完整性约束条件，它指定了插入到数据列中的可能值。它指定了插入到数据列中的可能值。 规则是规则是SQL Server 2005数据库中独立于表和视图的数据数据库中独立于表和视图的数据对象，与其作用的表是独立的，删除表时不会删除规则。对象，与其作用的表是独立的，删除表时不会删除规则。 规则就相当于数据表的数据验收员，当插入或修改的数规则就相当于数据表的数据

22、验收员，当插入或修改的数据放入数据表时，它就检查数据是否符合要求，符合要据放入数据表时，它就检查数据是否符合要求，符合要求的放入数据表，不符合的拒之门外。这样就保证了放求的放入数据表，不符合的拒之门外。这样就保证了放入数据表的数据是正确的。入数据表的数据是正确的。 如果列同时有与之相关联的默认值和规则，则默认值必如果列同时有与之相关联的默认值和规则，则默认值必须在规则定义的范围内。与规则冲突的默认值永远不能须在规则定义的范围内。与规则冲突的默认值永远不能被插入。被插入。定义规则对象 规则对象的使用方法与默认值对象使用步骤类型；规则对象的使用方法与默认值对象使用步骤类型；定义规则对象定义规则对象

23、将规则对象绑定到列或用户自定义类型将规则对象绑定到列或用户自定义类型 定义规则对象的语法结构定义规则对象的语法结构CREATE RULE rule AS condition_expression 将规则对象绑定到自定义类型或列将规则对象绑定到自定义类型或列语法格式：语法格式：sp_bindrule rulename = rule , objname = object_name , futureonly = futureonly_flag 规则对象例例1：创建一个规则，用于指定性别为：创建一个规则，用于指定性别为男男或者或者女女，并绑定到数据表，并绑定到数据表student的的ssex字段。字段

24、。 CREATE RULE ssex_ruleAS sex =男男 or sex =女女EXEC sp_bindrule ssex_rule , student.ssex例例2：创建一个规则，用于指定学号首位以：创建一个规则，用于指定学号首位以1-9开头，其他位以开头，其他位以0-9结尾，并绑定结尾，并绑定到数据表到数据表student的的sno字段。字段。CREATE RULE sno_ruleAS num like 1-90-90-9 0-9 0-9 0-9 0-9 0-9 0-9EXEC sp_bindrule sno_rule , student.sno2）删除规则对象 删除规则对象之

25、前，首先应使用系统存储过程删除规则对象之前，首先应使用系统存储过程sp_unbindrule解除被绑定对象与规则对象之间的绑定关解除被绑定对象与规则对象之间的绑定关系。系。解除规则绑定语法格式解除规则绑定语法格式： sp_unbindrule objname = object_name , futureonly = futureonly_flag删除规则语法格式删除规则语法格式：DROP rule rule , n例1：解除规则对象解除规则对象sdept_rule与数据表与数据表student的的sdept字段的绑定，然后删除规则对象字段的绑定，然后删除规则对象sdept_rule;If exists (select name from sysobjects where name = sdept_rule and type=R）BeginExec sp_unbindrule student.sdeptDrop rule sdept_ruleEnd 创建及绑定DEFAULT对象 创建: C R E AT E D E FA U LT d e f a u l t _ n a m e A S constant_expression例：C