TP05-数据库完整性

1、数据完整性基本概念数据完整性基本概念 使用使用约约束束标识列标识列IDENTITY2022-3-26第五章数据库完整性第五章数据库完整性数据完整性数据完整性 数据存放在表中 “数据完整性的问题大多是由于设计引起的” 创建表的时候，就应当保证以后数据输入是正确的错误的数据、不符合要求的数据不允许输入2022-3-26创建表：保证数据的完整性创建表：保证数据的完整性 = 实施完整性约束实施完整性约束数据完整性数据完整性 准确性准确性可靠性可靠性完整性内容举例完整性内容举例 输入的类型是否正确？年龄必须是数字 输入的格式是否正确？身份证号码必须是18位 是否在允许的范围内？性别只能是“男”或者“女”

2、 是否存在重复输入？学员信息输入了两次 是否符合其他特定要求？学生的出生日期必须小于入学日期 2022-3-26列值要求（约束）列值要求（约束）整行要求（约束）整行要求（约束）四种完整性约束四种完整性约束2022-3-26域完整性域完整性实体完整性实体完整性参照参照完完整性整性自定义完整性自定义完整性提供四种类型的约束保证完整性提供四种类型的约束保证完整性实体完实体完整性整性2022-3-26河南新乡河南新乡赵可以赵可以0010016河南新乡河南新乡张丽鹃张丽鹃0010015江西南昌江西南昌雷铜雷铜0010014湖南新田湖南新田吴兰吴兰0010013山东定陶山东定陶李山李山0010012.地址

3、地址姓名姓名学号学号江西南昌江西南昌雷铜雷铜0010014约束方法：唯一约束、主键约束、标识列约束方法：唯一约束、主键约束、标识列实体完整性：实体完整性：又称行完整性，要求表中不能存在完全相同的记录，而且每条记录都具有一个非空且不重复的主键值。域完整性域完整性2022-3-26河南新乡河南新乡赵可以赵可以河南新乡河南新乡张丽鹃张丽鹃0010015江西南昌江西南昌雷铜雷铜0010014湖南新田湖南新田吴兰吴兰0010013山东定陶山东定陶李山李山0010012.地址地址姓名姓名学号学号湖北江门湖北江门李亮李亮8700000000约束方法：限制数据类型、检查约束、外键约束、默认值、非空约束约束方法

4、：限制数据类型、检查约束、外键约束、默认值、非空约束0010016学学号号要要求求7位位字字符符域完整性：域完整性：又称列完整性，用以指定列的数据输入是否具有正确的数据类型、格式以及有效的数据范围。参照参照完完整性整性科目学号分数数学001001288数学001001374语文001001267语文001001381数学0010016982022-3-26河南新乡河南新乡赵可以赵可以0010016河南新乡河南新乡张丽鹃张丽鹃0010015江西南昌江西南昌雷铜雷铜0010014湖南新田湖南新田吴兰吴兰0010013山东定陶山东定陶李山李山0010012地址地址姓名姓名学号学号980010021数

5、学数学约束方法：外键约束约束方法：外键约束参照参照完整性：完整性：又称引用完整性，用以保证主表（被参照表）中的数据与从表（参照表）中数据的一致性。自定义完整性自定义完整性帐号姓名信用.00192孙悟空700288猪悟能612333段誉890111虚竹4093000岳不群-102022-3-26AV121322乔峰乔峰CV0016AV372133玄痛玄痛CV0015AV378291沙悟净沙悟净AV0014AV378290猪悟能猪悟能AV0013AV378289孙悟空孙悟空AV0012.会员证会员证用户姓名用户姓名用户编号用户编号约束方法：规则、存储过程、触发器约束方法：规则、存储过程、触发器触发

6、器：检查信用值触发器：检查信用值设置主键设置主键2022-3-26操作演示：设置主键操作演示：设置主键2022-3-26PRIMARY KEYPRIMARY KEY约束约束 该约束能确保数据的唯一性该约束能确保数据的唯一性 若若PRIMARY KEYPRIMARY KEY约束定义约束定义在多列上，则一列中的值可以在多列上，则一列中的值可以重复，但重复，但PRIMARY KEYPRIMARY KEY约束定约束定义中的所有列的组合的值必须义中的所有列的组合的值必须唯一唯一 强制表的实体性。不允许数据库强制表的实体性。不允许数据库表在指定列上具有相同的值，且表在指定列上具有相同的值，且不允许有空值。

7、不允许有空值。author_idtitle_idbookprice100001Prof_1Computer25100002Prof_2Nature35100001Prof_3English28100003Prof_4English312022-3-26组合主键组合主键PRIMARY KEYPRIMARY KEY约束约束 2022-3-26ALTER TABLE SubjectsALTER TABLE SubjectsADD CONSTRAINT PK_SubjectADD CONSTRAINT PK_SubjectPRIMARY KEY CLUSTEREDPRIMARY KEY CLUSTER

8、ED(PrimaryCode, SecondaryCode)(PrimaryCode, SecondaryCode)GOGO聚族索引PRIMARY KEYPRIMARY KEY约束约束 设置默认值设置默认值2022-3-26操作演示：设置默认值操作演示：设置默认值2022-3-26默认值默认值约束约束 若某列定义了默认值（若某列定义了默认值（defaultdefault）约束，用户在输入数）约束，用户在输入数据时，没有为该列指定数据，那么系统将默认值赋给据时，没有为该列指定数据，那么系统将默认值赋给该列。该列。语法：语法：constraint constraint 约束名约束名 default

9、 default 默认值约束表达式默认值约束表达式 for for 列名列名Eg Eg ：以创建表：以创建表readerreader，为其添加默认约束，使，为其添加默认约束，使“登记登记日期日期”列的默认值为系统时间。列的默认值为系统时间。 alter table reader alter table reader add add constraint df_djrq default getdate() constraint df_djrq default getdate() for for 登记日期登记日期创建检查约束创建检查约束2022-3-26操作演示：建立检查约束操作演示：建立检查约束

10、添加约束示例添加约束示例2-22022-3-26操作演示：查看约束结果操作演示：查看约束结果 2022-3-26用于限定输入到一列或多列的值的范围，从逻辑表达用于限定输入到一列或多列的值的范围，从逻辑表达式判断数据的有效性。式判断数据的有效性。语法：语法：constraint 约束名约束名 check（检查约束表达式）（检查约束表达式）例例：在：在表表reader上上定义检查约束，要求定义检查约束，要求“性别性别”列的列的输入值只能是输入值只能是“男男”或或“女女”。ALTER TABLE DepartmentsADD CONSTRAINT CK_sex check (性别性别=男男or性别性

11、别=女女)GO 检查检查约约束束 2022-3-26UNIQUEUNIQUE约束约束 该约束可确保在非主键列中不输入重复值，可该约束可确保在非主键列中不输入重复值，可以实现在一张表上定义以实现在一张表上定义多个多个UNIQUEUNIQUE约束，且在约束，且在定义有定义有UNIQUEUNIQUE约束的列上允许约束的列上允许有空有空值值可以对一个表定义多个可以对一个表定义多个 UNIQUE UNIQUE 约束，但约束，但只能定义一个只能定义一个 PRIMARY KEY PRIMARY KEY 约束约束UNIQUE UNIQUE 约束允许约束允许 NULL NULL 值，这一点与值，这一点与 PRI

12、MARY KEY PRIMARY KEY 约束不同，当与参与约束不同，当与参与 UNIQUE UNIQUE 约束的任何值一起使用时，每列只允许一约束的任何值一起使用时，每列只允许一个空值个空值 FOREIGN KEY FOREIGN KEY 约束可以引用约束可以引用 UNIQUE UNIQUE 约束约束2022-3-26例：在表例：在表DepartmentsDepartments上的上的TelCodeTelCode列上添加一个列上添加一个UNIQUEUNIQUE约束约束ALTER TABLE DepartmentsADD CONSTRAINT U_TelCode UNIQUE (TelCode

13、)GO UNIQUEUNIQUE约束约束 2022-3-26FOREIGN KEYFOREIGN KEY约束约束 该约束用于建立两个表一列该约束用于建立两个表一列或多列数据之间的链接的或多列数据之间的链接的将主键值将主键值(一列或多列一列或多列)添加到另一个表中，添加到另一个表中，可创建两个表之间的可创建两个表之间的链接，这个列就成为链接，这个列就成为第二个表的外键第二个表的外键该约束该约束能强制能强制引用完引用完整性整性建立主外键关系建立主外键关系2022-3-26操作演示：演示建立主外键关系操作演示：演示建立主外键关系主外键建立后注意事项主外键建立后注意事项2022-3-26 当主表中没有

14、对应的记录时，不能将记录添加到子表成绩表中不能出现在学员信息表中不存在的学号 不能更改主表中的值而导致子表中的记录孤立把学员信息表中的学号改变了，学员成绩表中的学号也应当随之改变 子表存在与主表对应的记录，不能从主表中删除该行不能把有成绩的学员删除了 删除主表前，先删子表先删学员成绩表、后删除学员信息表FOREIGN KEY约束约束 外健用于建立和加强两个表数据之外健用于建立和加强两个表数据之间的链接的一列或多列间的链接的一列或多列2022-3-262022-3-26被被FOREIGNFOREIGN KEYKEY 参照的列在参照的列在表中应该具有表中应该具有PRIMARYPRIMARY KEY

15、KEY约束或约束或 UNIQUEUNIQUE 约束约束 IDNameTelIDsalarytax主键主键外键外键FOREIGN KEY约束约束2022-3-26语法：语法：ALTERALTER TABLETABLE table_namename ADDADD CONSTRAINTCONSTRAINT constraint_namename FOREIGNFOREIGN KEYKEY(columncolumn_namename,.) REFERENCESREFERENCES ref_table (columncolumn_namename,.)Eg ：已创建主表：已创建主表student和从表和

16、从表score，定义主表，定义主表“sid”列为主键，添加从表中的列为主键，添加从表中的“sid”列为外键列为外键ALTERALTER TABLETABLE score ADDADD CONSTRAINTCONSTRAINT constraint_sid FOREIGNFOREIGN KEYKEY(sid) REFERENCESREFERENCES student (sid)FOREIGN KEY约束约束设置标识列设置标识列2022-3-26 表中没有合适的列作为主键怎么办？操作演示：设置标识列操作演示：设置标识列2022-3-26标识列标识列identityidentity每个表中都可以有一

17、个标识列。该列值由系统自动生每个表中都可以有一个标识列。该列值由系统自动生成，用以唯一标识插入到表中的每一行。成，用以唯一标识插入到表中的每一行。语法：语法： identityidentity（标识初始值，标识增量值）（标识初始值，标识增量值） Eg Eg ：在：在readerreader表创建时，定义表创建时，定义ridrid为标识列，其中标为标识列，其中标识初始值和增量值都为识初始值和增量值都为1 1。Create table reader(Create table reader(rid int not null identity(1,1) ,rid int not null identi

18、ty(1,1) ,Name char(10) not nullName char(10) not null) ) 思考思考2022-3-26 标识列允许为字符数据类型吗？ 如果标识列A的初始值为1，增长量为2，则输入三行数据以后，再删除两行，下次再输入数据行的时候，标识值从多少开始？删除约束删除约束查看表中的约束： exec sp_helpconstraint 表名2022-3-26ALTER TABLE 表名表名 DROP CONSTRAINT 约束名约束名 例如：删除例如：删除Student表中地址默认约束表中地址默认约束ALTER TABLE Student DROP CONSTRAIN

19、T DF_stuAddress演示示例演示示例8：删除：删除Student表约束表约束添加约束添加约束 添加约束的语法2022-3-26ALTER TABLE 表名表名 ADD CONSTRAINT 约束名约束名 约束类型约束类型 具体的约束说明具体的约束说明约束名的取名规则推荐采用：约束类型约束名的取名规则推荐采用：约束类型_约束列约束列主键（主键（Primary Key）约束：如）约束：如 PK_stuNo唯一（唯一（Unique Key）约束：如）约束：如 UQ_stuID默认（默认（Default Key）约束：如）约束：如 DF_stuAddress检查（检查（Check Key）约

20、束：如）约束：如 CK_stuBornDate外键（外键（Foreign Key）约束：如）约束：如 FK_stuNo 添加约束示例添加约束示例2-1如何在表Student中添加下面的约束？ 主键约束：学号 唯一约束：身份证号 默认约束：Address列的默认值是“地址不详” 检查约束：出生日期的值为1980年1月1日后 外键约束：设置GradeID列为外键，建立Grade表与Student表的引用关系2022-3-26ALTER TABLE Student ADD CONSTRAINT PK_stuNo PRIMARY KEY (StudentNo)ALTER TABLE Student ADD CONSTRAINT UQ_stuID UNIQUE (IDENTITYCard)ALTER TABLE Student ADD CONSTRAINT DF_stuAddress DEFAULT (地址不详地址不详) FOR