SQL数据完整性培训课件

引入数据完整性（DataIntegrity）是指存储在数据库中的数据，应该保持一致性和可靠性。在输入数据时，由于不可避免的种种原因，会发生输入无效或错误信息。如何保持输入的数据符合规定，是数据库系统，尤其是多用户的关系数据库系统首要关注的问题。7/22/20231SQL数据完整性学习目标默认(Default)规则(Rull)约束(Constraint)学习完本课程，您应该能够：7/22/20232SQL数据完整性知识重、难点分析重点：默认(Default)规则(Rull)和结构的修改难点：

约束(Constraint)7/22/20233SQL数据完整性数据完整性

数据完整性是用来确保数据库中的数据的正确性和可靠性。实体完整性：实体完整性是为了保证表中的数据唯一，实体完整性可由主键来实现。表中的主键在所有记录上的取值必须唯一。域完整性：域完整性可以保证数据的取值在有效的范围内。参照完整性：参照完整性用于确保相关联的表间的数据应保持一致，避免因一个表的记录修改，造成另一个表的内容变为无效的值。一般来说，参照完整性是通过外键和主键来维护的。自定义完整性：由用户自行定义的，不同于前面３种的完整性，也可以说一种强制数据定义。7/22/20234SQL数据完整性任务一约束7/22/20235SQL数据完整性约束类型功能说明primarykey保证主键的实体完整性主键的值不能有重复值，且不为null。使用时，往往在其上建立索引，以提高数据库的性能。unique保证非主键的实体完整性非主键(即非主关键字)的值不能有重复值。foreignkey保证参照完整性一个表中的值，须参照另外一个表中的主键值。check域完整性字段可以取值的范围。default域完整性为字段赋予特定的值。Notnull实体完整性非空约束。约束限制了用户可能输入到表或字段中的值。7/22/20236SQL数据完整性

一约束的定义

在SQLServer系统中，约束的定义主要是通过createtable语句或altertable语句来实现的。

使用createtable语句，是在建立新表的同时定义了约束。使用altertable语句，是向已经存在的表中添加约束。约束可以是字段级约束，也可以是表级约束。字段级约束是把约束放在某个字段列上的，且约束仅对该字段列起作用，表级约束是把约束放在表中的多个字段列上。7/22/20237SQL数据完整性1、使用createtable语句创建约束

createtabletable_name(column_namedata_type[defaultconstraint_expression][<table_constraint>::=[constraintconstraint_name]{[null|notnull]|[{primarykey|unique}[clustered|nonclustered]]|[foreignkey]referencesref_table[(ref_column)]|check[notforreplication]}][,…n])7/22/20238SQL数据完整性2、使用altertable语句创建约束

altertabletable_name{add{<column_constraint>::=[constraintconstraint_name]{[null|notnull]|[{primarykey|unique}]|[foreignkey]referencesref_table[(ref_column)]|defaultconstant_expression|check(logical_expression)}}[,…n]|drop{[constraint]constraint_name|columncolumn}[,…n]}7/22/20239SQL数据完整性

二primarykey约束在关系数据库中，每个表都有一个能够唯一地标识表中每一行(实体)的字段，即主码或称为主关键字。数据库系统是通过主码来保证表的实体完整性。

primarykey约束，反映了定义为主码的字段的取值不能重复，且不能取null值。创建primarykey约束时，SQLServer会自动创建一个唯一的簇索引。

7/22/202310SQL数据完整性1、使用企业管理器创建primarykey约束7/22/202311SQL数据完整性

2、使用查询分析器创建primarykey约束例：创建列级主键约束，无约束名。

use学生管理数据库gocreatetable学生表1(学号varchar(9)notnullprimarykey,姓名varchar(8),性别varchar(2),年龄tinyint,所在院系varchar(10),班级名varchar(10),入学年份datetime)7/22/202312SQL数据完整性

例：创建列级主键约束，有约束名。

use学生管理数据库gocreatetable学生表2(学号varchar(9)

constraintpk_学号notnullprimarykey,姓名varchar(8),性别varchar(2),年龄tinyint,所在院系varchar(10),班级名varchar(10),入学年份datetime,)2、使用查询分析器创建primarykey约束7/22/202313SQL数据完整性

例：创建列级主键约束，有约束名。

use学生管理数据库gocreatetable学生表2(学号varchar(9),姓名varchar(8),性别varchar(2),年龄tinyint,所在院系varchar(10),班级名varchar(10),入学年份datetime,

constraintpk_学号notnullprimarykey(学号))2、使用查询分析器创建primarykey约束7/22/202314SQL数据完整性

例：向表添加列级主键约束，有约束名。

use学生管理数据库goaltertable学生表2addconstraintpk_学号primarykey(学号)3、使用查询分析器创建primarykey约束7/22/202315SQL数据完整性

三unique约束unique约束对某个字段列提供了实体完整性。

unique约束主要用在非主键的列上限制数据的唯一。按照unique约束的要求,在一个表中不允许任意两行在被约束的字段列上有相同的非空值。一个表可以有多个unique约束。7/22/202316SQL数据完整性1、使用企业管理器创建unique约束7/22/202317SQL数据完整性例：创建列级唯一约束，无约束名。

use学生管理数据库gocreatetable课程表3(课程号varchar(4)notnullprimarykey,课程名varchar(12)unique,先修课varchar(4),)2、使用查询分析器创建unique约束7/22/202318SQL数据完整性例：创建列级唯一约束，有约束名。

use学生管理数据库gocreatetable课程表3(课程号varchar(4)notnullprimarykey,课程名varchar(12),先修课varchar(4),

constraintu_课程名unique(课程名))2、使用查询分析器创建unique约束7/22/202319SQL数据完整性

例：向表添加列级唯一约束，有约束名。

use学生管理数据库goaltertable课程表3addconstraintu_课程名unique(课程名)3、使用查询分析器创建unique约束7/22/202320SQL数据完整性

四foreignkey约束

外码(外关键字)是说明某列的取值必须参照另外表的主码值。一个外码是一个表的主码并且是另一个表的外码。外码提供了两个表之间的连接。外码通过强制外码字段的取值必须是另外表中主码字段的有效值，来实施参照完整性。

7/22/202321SQL数据完整性

1、使用企业管理器创建foreignkey约束7/22/202322SQL数据完整性

use学生管理数据gocreatetable成绩表(学号varchar(9)constraintfk_学号notnullforeignkeyreferences学生表(学号),

课程号varchar(4)constraintfk_课程号notnullforeignkeyreferences课程表(课程号),constraintpk_学号_课程号primarykey(学号,课程号),

成绩tinyint)2、使用查询分析器创建foreignkey约束7/22/202323SQL数据完整性例：向表添加列级外键约束，有约束名。use学生管理数据库goaltertable成绩表1

withcheckaddconstraintfk_课程号1foreignkey(课程号)references课程表1(课程号)2、使用查询分析器创建foreignkey约束7/22/202324SQL数据完整性五default约束当用户没有为定义为default约束的字段输入数据时,由default约束提供默认的值。例：use学生数据库gocreatetable成绩表(学号varchar(9)constraintfk_学号notnullforeignkeyreferences学生表(学号),课程号varchar(4)constraintfk_课程号notnullforeignkeyreferences课程表(课程号),constraintpk_学号_课程号primarykey(学号,课程号),成绩tinyintconstraintd_成绩default'0')7/22/202325SQL数据完整性六check约束check约束限制了字段的取值范围。与规则相似，check约束限制了向特定的字段列输入数据的类型。表级定义的check约束可以对多个字段列进行核查。7/22/202326SQL数据完整性1、使用企业管理器创建check约束7/22/202327SQL数据完整性例：创建列级核查约束，有约束名。use学生管理数据库gocreatetable成绩表(学号varchar(9)constraintfk_学号notnullforeignkeyreferences学生表(学号),课程号varchar(4)constraintfk_课程号notnullforeignkeyreferences课程表(课程号),constraintpk_学号_课程号primarykey(学号,课程号),成绩tinyintconstraintd_成绩default'0'

constraintc_成绩check(成绩between0and100))2、使用查询分析器创建check约束7/22/202328SQL数据完整性例：创建列级核查约束，无约束名。use学生管理数据库gocreatetable成绩表1(学号varchar(9),课程号varchar(4),成绩tinyint

check(成绩between0and100))2、使用查询分析器创建check约束7/22/202329SQL数据完整性例：向表添加列级核查约束，有约束名。use学生管理数据库goaltertable成绩表1

withcheckaddconstraintc_成绩check(成绩between0and100)2、使用查询分析器创建check约束7/22/202330SQL数据完整性3.7使用idendity列

标示为idendity属性的列不必在插入新行时为其赋值,服务器会自动为其设置一个唯一的行序列号。例：

createtable学生表2(学号smallintidentity(1,1)primarykey,姓名varchar(8),性别varchar(2),年龄tinyint,所在院系varchar(10),班级名varchar(10),入学年份datetime)7/22/202331SQL数据完整性七查看约束Execsp_helpcinstrainttable_name7/22/202332SQL数据完整性1、使用企业管理器删除约束八删除约束7/22/202333SQL数据完整性

2、使用查询分析器删除约束

use学生管理数据库goaltertable成绩表dropconstraintc_成绩

删除约束7/22/202334SQL数据完整性默认是一种数据库对象，它与默认值约束的功能一样。若用户输入记录时，在没有给出具体的数据的字段中，系统会自动插入一个数据。默认值用来向无值的列(字段)提供一个预先指定的值。和规则一样，默认在创建后,必须和某一列绑定才能生效。任务二默认7/22/202335SQL数据完整性

一创建默认1、使用企业管理器创建默认7/22/202336SQL数据完整性1、使用企业管理器创建默认7/22/202337SQL数据完整性

创建默认2、使用createdefault语句创建默认

createdefault<默认名称>as<常量表达式>功能:

创建称为默认值的对象。默认值定义是限制列数据的首选，也是标准的方法，因为定义和表存储在一起，当除去表时，将自动除去默认值定义。常量表达式只包含常量值的表达式，不能包含任何或其他数据库对象的名称。可以使用任何常量、内建函数或数学表达式。例：演示创建默认的使用。

use学生管理数据库

gocreatedefaulttime_defaultasgetdate()7/22/202338SQL数据完整性查看缺省值7/22/202339SQL数据完整性查看缺省值7/22/202340SQL数据完整性用存储过程Sp_helptext查看缺省值例：

查看缺省值todayexecsp_helptexttoday运行结果如下------------------------------------------------------------------Text------------------------------------------------------------------createdefault[dp_date]asgetdate()7/22/202341SQL数据完整性

二

绑定默认1、使用企业管理器绑定默认7/22/202342SQL数据完整性绑定列7/22/202343SQL数据完整性

绑定默认2、使用sp_bindefault语句绑定默认

sp_bindefault<默认名>,<‘表.字段名’>例：演示绑定默认的使用。use学生管理数据库gosp_bindefaulttime_default,'学生表.入学年份'7/22/202344SQL数据完整性三解除绑定使用企业管理器解除绑定

使用sp_unbindefault解除绑定

sp_unbindefault<‘表.字段名’>

例：演示解除绑定的使用

use学生课程数据库

go

sp_unbindefault'学生表.入学年份'

7/22/202345SQL数据完整性四删除默认使用企业管理器删除默认使用dropdefault删除默认

dropdefault<默认名称>

例：演示删除默认的使用。

use学生课程数据库

go

dropdefaulttime_default7/22/202346SQL数据完整性

注:默认的使用限制

通过default子句为列（字段）定义默认值或通过sp_unbindefault绑定默认，两者只能选择一种方式。

默认只能用来设置常量或者SQLServer函数值。每列(字段)只能与一个默认绑定。确保默认的数据类型与其绑定列的数据类型一致。确保默认值与该列的规则一致。如果默认已经与某列绑定，那么就不能删除它，除非在删除之前，已解除默认和列或数据类型的所有绑定关系。

7/22/202347SQL数据完整性规则就是数据库中，对存储在数据库中表的列(字段)的规定和限制。规则是单独存储的独立的数据库对象，规则与作用的表是相互独立的，即表的删除修改，不会对与之相连的规则产生影响。规则和约束可以同时使用表的列，可以有一个规则及多个(check)约束规则。规则提供了一种加强列(字段)域约束的机制。规则要在insert和update语句之前给出。任务三规则7/22/202348SQL数据完整性

一创建规则1、使用企业管理器创建规则7/22/202349SQL数据完整性

2、使用createrule语句创建规则格式：createrulerule_nameascondition_expression说明：

rule_name:新规则的名称。

condition_expression:定义规则的条件。规则可以是where子句中任何有效的表达式，并且可以包含诸如算术运算符，关系运算符和in、like、between之类的元素。规则不能引用列或其他数据库对象.可以包含不引用数据库对象的内置函数。

condition_expression包含一个变量，每个局部变量的前面都有一个@符号，该表达式引用通过update或insert语句输入的值。在创建规则时，可使用任何名称或符号表示值,但第一个字符必须是@符号。7/22/202350SQL数据完整性例：创建考试成绩规则score_rule。use学生课程数据库gocreaterulecj_scopeas@cj_scopebetween0and100例:创建雇佣日期规则hire_date_rule。createrulehire_date_ruleas@hire_date>='1980-01-01'and@hire_date<=getdate()7/22/202351SQL数据完整性查看规则

7/22/202352SQL数据完整性用存储过程Sp_helptext查看规则使用Sp_helptext存储过程可以查看规则的细节：sp_helptext[@objname=]'name'7/22/202353SQL数据完整性例：

查看规则hire_date_ruleexecsp_helptexthire_date_rule运行结果如下Text--------------------------------------------------------------------------------------createrulehire_date_ruleas@hire_date>='1980-01-01'and@hire_date<=getdate()7/22/202354SQL数据完整性

二绑定规则使用企业管理器绑定规则使用sp_bindrule绑定规则

sp_bindrule

<规则名称>,<‘表.字段名’>

将规则绑定到列(字段)。绑定规则时，必须使用引号作为分隔符，参数中的圆点(.)将传递给sp_bindrule

。例：演示绑定规则的使用。