SQL数据完整性培训课件

《SQL数据完整性培训课件》由会员分享，可在线阅读，更多相关《SQL数据完整性培训课件（62页珍藏版）》请在装配图网上搜索。

1、引入引入 数据完整性（数据完整性（Data IntegrityData Integrity）是指存）是指存储在数据在数据库中中的数据，的数据，应该保持一致性和可靠性。在保持一致性和可靠性。在输入数据入数据时，由，由于不可避免的种种原因，会于不可避免的种种原因，会发生生输入无效或入无效或错误信息。信息。如何保持如何保持输入的数据符合入的数据符合规定，是数据定，是数据库系系统，尤其是，尤其是多用多用户的关系数据的关系数据库系系统首要关注的首要关注的问题。4/26/20241SQL数据完整性学学习目目标v默默认(Default)v规则(Rull)v约束束(Constraint)学习完本课程，您应该能

2、够：学习完本课程，您应该能够：4/26/20242SQL数据完整性知知识重、重、难点分析点分析重点：l默默认(Default)l规则(Rull)v和结构的修改难点：点：l 约束(Constraint)4/26/20243SQL数据完整性数据完整性数据完整性 数据完整性是用来确保数据库中的数据的正确性和可靠性。l实体完整性：实体完整性：实体完整性是为了保证表中的数据唯一，实体完整性可由主键来实现。表中的主键在所有记录上的取值必须唯一。l域完整性：域完整性：域完整性可以保证数据的取值在有效的范围内。l参照完整性：参照完整性：参照完整性用于确保相关联的表间的数据应保持一致，避免因一个表的记录修改，造

3、成另一个表的内容变为无效的值。一般来说，参照完整性是通过外键和主键来维护的。l自定义完整性：自定义完整性：由用户自行定义的，不同于前面种的完整性，也可以说一种强制数据定义。4/26/20244SQL数据完整性 任务一任务一 约束约束4/26/20245SQL数据完整性约束类型功 能 说 明primary keyprimary key保保证主主键的的实体完整性体完整性主主键的的值不能有重复不能有重复值，且不且不为nullnull。使用。使用时，往，往往在其上建立索引，以提往在其上建立索引，以提高数据高数据库的性能。的性能。uniqueunique保保证非主非主键的的实体完整性体完整性非主非主键(

4、即非主关即非主关键字字)的的值不能有重复不能有重复值。foreign keyforeign key保保证参照完整性参照完整性一个表中的一个表中的值，须参照另参照另外一个表中的主外一个表中的主键值。checkcheck域完整性域完整性字段可以取字段可以取值的范的范围。defaultdefault域完整性域完整性为字段字段赋予特定的予特定的值。Not nullNot null实体完整性体完整性非空非空约束。束。约束限制了用户可能输入到表或字段中的值。4/26/20246SQL数据完整性 一一 约束的定义约束的定义 在SQL Server系统中，约束的定义主要是通过create table语句或al

5、ter table语句来实现的。l 使用create table语句，是在建立新表的同时定义了约束。l 使用alter table语句，是向已经存在的表中添加约束。l 约束可以是字段级约束，也可以是表级约束。字段级约束是把约束放在某个字段列上的，且约束仅对该字段列起作用，表级约束是把约束放在表中的多个字段列上。4/26/20247SQL数据完整性 1 1、使用、使用create tablecreate table语句创建约束语句创建约束 create table table_name (column_name data_type default constraint_expression :=

6、constraint constraint_name null|not null|primary key|unique clustered|nonclustered|foreign key references ref_table (ref_column)|check not for replication ,n )4/26/20248SQL数据完整性 2 2、使用、使用alter tablealter table语句创建约束语句创建约束 alter table table_name add :=constraint constraint_name null|not null|primary

7、key|unique|foreign key references ref_table (ref_column)|default constant_expression|check(logical_expression),n|drop constraint constraint_name|column column ,n 4/26/20249SQL数据完整性 二二 primary key primary key 约束约束 在关系数据库中，每个表都有一个能够唯一地标识表中每一行(实体)的字段，即主码或称为主关键字。l 数据库系统是通过主码来保证表的实体完整性。l primary key 约束，反

8、映了定义为主码的字段的取值不能重复，且不能取null值。l 创建primary key约束时，SQL Server会自动创建一个唯一的簇索引。4/26/202410SQL数据完整性1、使用企业管理器创建primary key约束4/26/202411SQL数据完整性 2、使用查询分析器创建、使用查询分析器创建primary key约束约束例：创建列级主键约束，无约束名。例：创建列级主键约束，无约束名。use 学生管理数据库 go create table 学生表1 (学号 varchar(9)not null primary key,姓名 varchar(8),性别 varchar(2),年龄

9、 tinyint,所在院系 varchar(10),班级名 varchar(10),入学年份 datetime )4/26/202412SQL数据完整性 例：创建列级主键约束，有约束名。例：创建列级主键约束，有约束名。use 学生管理数据库 go create table 学生表2 (学号 varchar(9)constraint pk_学号 not null primary key,姓名 varchar(8),性别 varchar(2),年龄 tinyint,所在院系 varchar(10),班级名 varchar(10),入学年份 datetime,)2、使用查询分析器创建、使用查询分析器

10、创建primary key约束约束4/26/202413SQL数据完整性 例：创建列级主键约束，有约束名。例：创建列级主键约束，有约束名。use 学生管理数据库 go create table 学生表2 (学号 varchar(9),姓名 varchar(8),性别 varchar(2),年龄 tinyint,所在院系 varchar(10),班级名 varchar(10),入学年份 datetime,constraint pk_学号 not null primary key(学号)2、使用查询分析器创建、使用查询分析器创建primary key约束约束4/26/202414SQL数据完整性

11、例：向表添加列级主键约束，有约束名。例：向表添加列级主键约束，有约束名。use 学生管理数据库 go alter table 学生表2 add constraint pk_学号 primary key(学号)3、使用查询分析器创建、使用查询分析器创建primary key约束约束4/26/202415SQL数据完整性 三三 unique unique约束约束 unique约束对某个字段列提供了实体完整性。l unique约束主要用在非主键的列上限制数据的唯一。l按照unique约束的要求,在一个表中不允许任意两行在被约束的字段列上有相同的非空值。l一个表可以有多个unique约束。4/26/2

12、02416SQL数据完整性1、使用企业管理器创建、使用企业管理器创建unique约束约束4/26/202417SQL数据完整性例：创建列级唯一约束，无约束名。use 学生管理数据库gocreate table 课程表3 (课程号 varchar(4)not null primary key,课程名 varchar(12)unique,先修课 varchar(4),)2、使用查询分析器创建、使用查询分析器创建unique约束约束4/26/202418SQL数据完整性例：创建列级唯一约束，有约束名。use 学生管理数据库 go create table 课程表3 (课程号 varchar(4)no

13、t null primary key,课程名 varchar(12),先修课 varchar(4),constraint u_课程名 unique(课程名)2、使用查询分析器创建、使用查询分析器创建unique约束约束4/26/202419SQL数据完整性 例：向表添加列级唯一约束，有约束名。例：向表添加列级唯一约束，有约束名。use 学生管理数据库 go alter table 课程表3 add constraint u_课程名 unique(课程名)3、使用查询分析器创建、使用查询分析器创建unique约束约束4/26/202420SQL数据完整性 四四 foreign key forei

14、gn key 约束约束 外码(外关键字)是说明某列的取值必须参照另外表的主码值。一个外码是一个表的主码并且是另一个表的外码。l 外码提供了两个表之间的连接。l 外码通过强制外码字段的取值必须是另外表中主码字段的有效值，来实施参照完整性。4/26/202421SQL数据完整性 1、使用企业管理器创建、使用企业管理器创建foreign key约束约束4/26/202422SQL数据完整性 use use 学生管理数据学生管理数据 go gocreate table create table 成成绩绩表表 (学号学号 varchar(9)varchar(9)constraint fk_constra

15、int fk_学号学号 not null foreign key references not null foreign key references 学生表学生表(学号学号),课课程号程号 varchar(4)varchar(4)constraint fk_constraint fk_课课程号程号 not null foreign key references not null foreign key references 课课程表程表(课课程号程号),constraint pk_constraint pk_学号学号_ _课课程号程号 primary key(primary key(学号学号

16、,课课程号程号),),成成绩绩 tinyint tinyint)2、使用查询分析器创建、使用查询分析器创建foreign key约束约束4/26/202423SQL数据完整性例：向表添加列级外键约束，有约束名。use use 学生管理数据学生管理数据库 go go alter table alter table 成成绩表表1 1 with checkwith check add constraint fk_ add constraint fk_课程号程号1 foreign key(1 foreign key(课程号程号)references)references 课程表程表1(1(课程号程号)

17、2、使用查询分析器创建、使用查询分析器创建foreign key约束约束4/26/202424SQL数据完整性五五 default 约束约束 当用户没有为定义为default约束的字段输入数据时,由default 约束提供默认的值。例：use 学生数据库gocreate table 成绩表 (学号 varchar(9)constraint fk_学号 not null foreign key references 学生表(学号),课程号 varchar(4)constraint fk_课程号 not null foreign key references 课程表(课程号),constraint

18、 pk_学号_课程号 primary key(学号,课程号),成绩 tinyintconstraint d_成绩 default 0 )4/26/202425SQL数据完整性六六 check check约束约束 check约束限制了字段的取值范围。l 与规则相似，check约束限制了向特定的字段列输入数据的类型。l 表级定义的check约束可以对多个字段列进行核查。4/26/202426SQL数据完整性1、使用企业管理器创建、使用企业管理器创建check约束约束4/26/202427SQL数据完整性例：创建列级核查约束，有约束名。use use 学生管理数据学生管理数据库库gogocreate

19、 table create table 成成绩绩表表 (学号学号 varchar(9)varchar(9)constraint fk_ constraint fk_学号学号 not null foreign key references not null foreign key references 学生表学生表(学号学号),),课课程号程号 varchar(4)varchar(4)constraint fk_ constraint fk_课课程号程号 not null foreign key references not null foreign key references 课课程表程表(

20、课课程号程号),),constraint pk_ constraint pk_学号学号_ _课课程号程号 primary key(primary key(学号学号,课课程号程号),),成成绩绩 tinyint tinyint constraint d_ constraint d_成成绩绩 default 0 default 0 constraint c_constraint c_成成绩绩 check(check(成成绩绩 between 0 and 100)between 0 and 100)2、使用查询分析器创建、使用查询分析器创建check约束约束4/26/202428SQL数据完整性例：

21、创建列级核查约束，无约束名。use use 学生管理数据学生管理数据库库gogocreate table create table 成成绩绩表表1 1 (学号学号 varchar(9),varchar(9),课课程号程号 varchar(4),varchar(4),成成绩绩 tinyint tinyint check(check(成成绩绩 between 0 and 100)between 0 and 100)2、使用查询分析器创建、使用查询分析器创建check约束约束4/26/202429SQL数据完整性例：向表添加列级核查约束，有约束名。use use 学生管理数据学生管理数据库 go g

22、o alter table alter table 成成绩表表1 1 with checkwith check add add constraint c_constraint c_成成绩绩 check(check(成成绩绩 between 0 and 100)between 0 and 100)2、使用查询分析器创建、使用查询分析器创建check约束约束4/26/202430SQL数据完整性3.7 使用idendity列 标示为idendity属性的列不必在插入新行时为其赋值,服务器会自动为其设置一个唯一的行序列号。例：create table create table 学生表学生表2 2 (

23、学号学号 smallint identity(1,1)primary key,smallint identity(1,1)primary key,姓名姓名 varchar(8),varchar(8),性性别 varchar(2),varchar(2),年年龄 tinyint,tinyint,所在院系所在院系 varchar(10),varchar(10),班班级名名 varchar(10),varchar(10),入学年份入学年份 datetime datetime )4/26/202431SQL数据完整性七 查看约束Exec sp_helpcinstraint table_name4/26/

24、202432SQL数据完整性1 1、使用企业管理器删除约束、使用企业管理器删除约束八八 删除约束删除约束4/26/202433SQL数据完整性 2 2、使用查询分析器删除约束、使用查询分析器删除约束 use 学生管理数据库 go alter table 成绩表 drop constraint c_成绩 删除约束删除约束4/26/202434SQL数据完整性 默认是一种数据库对象，它与默认值约束的功能一样。若用户输入记录时，在没有给出具体的数据的字段中，系统会自动插入一个数据。l 默认值用来向无值的列(字段)提供一个预先指定的值。l 和规则一样，默认在创建后,必须和某一列绑定才能生效。任务二任务

25、二 默认默认4/26/202435SQL数据完整性 一一 创建默认创建默认1、使用企业管理器创建默认使用企业管理器创建默认4/26/202436SQL数据完整性1、使用企业管理器创建默认4/26/202437SQL数据完整性 创建默认创建默认2 2、使用、使用create defaultcreate default语句创建默认语句创建默认 create default as 功能:l 创建称为默认值的对象。l 默认值定义是限制列数据的首选，也是标准的方法，因为定义和表存储在一起，当除去表时，将自动除去默认值定义。l 常量表达式只包含常量值的表达式，不能包含任何或其他数据库对象的名称。可以使用任

26、何常量、内建函数或数学表达式。例：演示创建默认的使用。use 学生管理数据库 go create default time_default as getdate()4/26/202438SQL数据完整性查查看缺省看缺省值值4/26/202439SQL数据完整性查看缺省值4/26/202440SQL数据完整性用存储过程用存储过程Sp_helptext 查看缺省值查看缺省值例：例：查看缺省看缺省值todayexec sp_helptext today运行运行结果如下果如下-Text-create default dp_date as getdate()4/26/202441SQL数据完整性 二二

27、绑定默认绑定默认1、使用企业管理器绑定默认使用企业管理器绑定默认4/26/202442SQL数据完整性绑定列定列4/26/202443SQL数据完整性 绑定默认绑定默认2 2、使用、使用sp_bindefaultsp_bindefault语句绑定默认语句绑定默认 sp_bindefault,例：演示绑定默认的使用。use 学生管理数据库 go sp_bindefault time_default,学生表.入学年份4/26/202444SQL数据完整性三三 解除绑定解除绑定n 使用企业管理器解除绑定使用企业管理器解除绑定 n 使用使用sp_unbindefaultsp_unbindefault解

28、除绑定解除绑定 sp_unbindefault 例：演示解除绑定的使用 use 学生课程数据库 go sp_unbindefault 学生表.入学年份 4/26/202445SQL数据完整性四四 删除默认删除默认n 使用企业管理器删除默认使用企业管理器删除默认n 使用使用drop defaultdrop default删除默认删除默认 drop default 例：演示删除默认的使用。use 学生课程数据库 go drop default time_default4/26/202446SQL数据完整性 注注:默认的使用限制默认的使用限制l 通过default子句为列（字段）定义默认值或通过sp

29、_unbindefault绑定默认，两者只能选择一种方式。l 默认只能用来设置常量或者SQL Server 函数值。l 每列(字段)只能与一个默认绑定。l 确保默认的数据类型与其绑定列的数据类型一致。l 确保默认值与该列的规则一致。l 如果默认已经与某列绑定，那么就不能删除它，除非在删除之前，已解除默认和列或数据类型的所有绑定关系。4/26/202447SQL数据完整性 规则就是数据库中，对存储在数据库中表的列(字段)的规定和限制。l 规则是单独存储的独立的数据库对象，规则与作用的表是相互独立的，即表的删除修改，不会对与之相连的规则产生影响。规则和约束可以同时使用表的列，可以有一个规则及多个(

30、check)约束规则。l 规则提供了一种加强列(字段)域约束的机制。l 规则要在insert和update语句之前给出。任务三任务三 规则规则4/26/202448SQL数据完整性 一一 创建规则创建规则1、使用企业管理器创建规则、使用企业管理器创建规则4/26/202449SQL数据完整性 2、使用使用create rulecreate rule语句创建规则语句创建规则 格式：格式：create rule rule_name as condition_expression 说明：说明：l rule_name:新规则的名称。l condition_expression:定义规则的条件。规则可以

31、是where子句中任何有效的表达式，并且可以包含诸如算术运算符，关系运算符和in、like、between之类的元素。l 规则不能引用列或其他数据库对象.可以包含不引用数据库对象的内置函数。l condition_expression包含一个变量，每个局部变量的前面都有一个符号，该表达式引用通过update或insert语句输入的值。l 在创建规则时，可使用任何名称或符号表示值,但第一个字符必须是符号。4/26/202450SQL数据完整性例：创建考试成绩规则score_rule。use 学生课程数据库 go create rule cj_scope as cj_scope between 0

32、 and 100例:创建雇佣日期规则hire_date_rule。create rule hire_date_rule as hire_date=1980-01-01 and hire_date=1980-01-01 and hire_date=1980-01-01 and hire_date=getdate()4/26/202454SQL数据完整性 二二 绑定规则绑定规则n 使用企业管理器绑定规则使用企业管理器绑定规则 n 使用使用sp_bindrulesp_bindrule绑定规则绑定规则 sp_bindrule,l 将规则绑定到列(字段)。l 绑定规则时，必须使用引号作为分隔符，参数中的

33、圆点(.)将传递给sp_bindrule。例：演示绑定规则的使用。use 学生管理数据库 go sp_bindrule score_rule,成绩表.成绩 go insert 成绩表 values(200203002,c801,125)4/26/202455SQL数据完整性规则绑定到用户的自定义类型规则绑定到表的列4/26/202456SQL数据完整性三三 解除绑定解除绑定n 使用企业管理器解除绑定使用企业管理器解除绑定n 使用使用sp_unbindrulesp_unbindrule解除绑定解除绑定 sp_unbindrule 例：演示解除绑定的使用。use 学生课程数据库 go sp_unb

34、indrule 成绩表.成绩4/26/202457SQL数据完整性四四 删除规则删除规则n 使用企业管理器删除规则n 使用drop rule删除规则 drop rule 例：演示删除规则的使用。use 学生课程数据库 go drop rule score_rule 4/26/202458SQL数据完整性 注注:使用规则的限制条件使用规则的限制条件l 规则只能处理常量、函数，不能用来查找表，也不能用来比较表中的列。l 表中的每列只能与一条规则绑定。如果某列已经绑定了一条规则，而又为该列绑定了一条新规则，那么旧规则将被新规则代替。l 如果规则与某列绑定，那么规则不能被直接删除，须先解除规则与所有的

35、列和数据库类型绑定关系，然后才能用drop rule命令删除。l 向系统中大量拷贝数据时，规则不起作用。l 在使用规则时，要确保规则中的值与其绑定列的数据类型相一致。4/26/202459SQL数据完整性总结总结v数据库的完整性是为了保证数据库中存储的数据数据库的完整性是为了保证数据库中存储的数据是正确的是正确的v完整性常用完整性常用类型：型：NOT NULL，CHECK、UNIQUE、PRIMARY KEY、FOREIGN KEY、DEFAULT v规则是用来限制取是用来限制取值范范围的的v使用默使用默认值4/26/202460SQL数据完整性综合合练习1.1.学生表有学号、姓名、性别、年龄

36、、备注。现在把以下约束学生表有学号、姓名、性别、年龄、备注。现在把以下约束（主键，（主键，uniqueunique，checkcheck，defaultdefault，规则）等尽可能的加在学生，规则）等尽可能的加在学生表里，最后把约束删除掉。表里，最后把约束删除掉。A.A.学号以学号以STST开头开头B.B.课程号为主键课程号为主键C.C.课程名为课程名为UNIUEUNIUE约束约束D.D.性别只能为男和女性别只能为男和女E.E.年龄在年龄在0-1000-100之间之间4/26/202461SQL数据完整性作业作业1.简述数据完整性的概念。述数据完整性的概念。2.如何如何创建建规则,使用使用规则,删除除规则?3.如何如何创建默建默认值,使用默使用默认值,删除默除默认值?4/26/202462SQL数据完整性