【SQL】数据库完整性使用实例

一、本文提供了数据库完整性的实例以及相应操作

背景知识
一、数据库完整性就是保证数据库中的数据的正确性和一致性，不会出现非法数据
二、数据库实现完整性：约束、默认、规则、触发器。

触发器实现完整性请看之前的文章链接: link.

名称解释（不区分大小写）

• student学生表：
  • 包含属性列：sno学号、sname学生姓名、age年龄、sex性别、dno学院编号、birthday生日
• sc选课信息表：
  • 包含属性列：sno学号、cno课程号、grade成绩
• dept学院信息表：
  • 包含属性列：dno学院编号、dname学院名称、dean学院负责人
• course课程信息表：
  • 包含属性列：cno课程号、cname课程名称、tname老师名称、credit学分、room教室

下面语句可以直接复制到SQL Server运行

• 运行方法，选中执行语句

方法不唯一，有问题欢迎留言讨论！代码块见文章末尾

二、代码

–在 XSGL 数据库中，创建一个名为 yanshi_student 的表，指定 SNO 为主码，且设置属性级约束、元组级约束和表级约束。
create table yanshi_student(
sno char(20),
sname char(20),
age int null,
sex char(2) default ‘男’,
dno char(4) not null,
birthday datetime null,
primary key(sno),
check (sex in (‘男’‘’,‘女’)),
CHECK(SNO LIKE ‘[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’)
/SNO 只能为 8 位数字，且不能以 0 开头/ ,
/定义了元组中 SNO 和 DNO 两个属性值之间的约束条件，SNO 的第 5 位只能为
1，2，3，且当第 5 位为 1 时，DNO 只能为 0001，第 5 位为 2 时，DNO 只能为 0002，第 5
位为 3 时，DNO 只能为 0003/
CHECK((SUBSTRING(SNO,5,1)=‘3’ AND DNO=‘0003’) OR (SUBSTRING(SNO,5,1) =‘2’ AND DNO=‘0002’) OR (SUBSTRING(SNO,5,1) = ‘1’ AND DNO=‘0001’))
)
–无法执行，sno为8位且只能是数字
INSERT yanshi_student(SNO,SNAME,DNO) values(‘x0001’,‘li’,‘0001’)
INSERT yanshi_student(SNO,SNAME,DNO) values(‘00001’,‘li’,‘0001’)
INSERT yanshi_student(SNO,SNAME,DNO) values(‘10001’,‘li’,‘0001’)

INSERT yanshi_student(SNO,SNAME,DNO) values(‘20073001’,‘li’,‘0001’)
INSERT yanshi_student(SNO,SNAME,DNO) values(‘20073001’,‘li’,‘0003’)

–在 XSGL 数据库中，创建一个规则，限定绑定列的输入值在 0.5 和 5 之间，并使用存储过程 sp_bindrule 将其绑定到 course 表中的 CREDIT 列中，限定学分只能在 0.5和 5 之间。
create rule rule1
as @c1 between 0.5 and 5
exec sp_bindrule ‘rule1’,‘course.credit’

–解除 rule1 规则到 yanshi_course.CREDIT 的绑定，并删除该规则。
EXEC sp_unbindrule ‘yanshi_course.CREDIT’
DROP RULE rule1

–（1） 创建一个表 yanshi_depart，包含 DNO、DNAME、DADDRESS 和 DEAN 四个属性，DNO 由 4 个数字字符构成，且为该表的主码，DNAME 由 30 个字符构成，DADDRESS 由5 个字符构成，DEAN 由 10 个字符构成。
create table yanshi_depart(
dno char(4),
dname char(30),
daddress char(5),
dean char(10),
primary key(dno)
)

–（2） 为例 1 中的 yanshi_student 表建立外键“DNO”，参考表 yanshi_depart 的“DNO”列。
create table yanshi_student(
sno char(20),
sname char(20),
age int null,
sex char(2) default ‘男’,
dno char(4) not null,
birthday datetime null,
primary key(sno),
foreign key(dno) references yanshi_depart(dno),
check (sex in (‘男’‘’,‘女’)),
CHECK(SNO LIKE ‘[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’)
)

–（3） 为表 yanshi_depart 的 DNAME 建立一个规则：@dname IN (‘机电学院’,‘信息学院’,‘工商学院’)。
create rule rule2
as
@dname in (‘机电学院’,‘信息学院’,‘工商学院’)
exec sp_bindrule ‘rule2’, ‘yanshi_depart.dname’

–（4） 为 表 yanshi_depart 的 DADDRESS 建 立 一 个 规 则 ： @daddress LIKE’[A-Z][1-9][1-9][1-9][1-9]‘，限定 DADDRESS 的值只能由字母开头，后跟 4 个数字。
create rule rule3
as
@daddress LIKE’[A-Z][1-9][1-9][1-9][1-9]’
exec sp_bindrule ‘rule3’,‘yanshi_depart.daddress’

–（5） 删除第 3 小题所建立的规则。
exec sp_unbindrule ‘yanshi_depart.daddress’
drop rule rule3

三、SQL代码块

--在 XSGL 数据库中，创建一个名为 yanshi_student 的表，指定 SNO 为主码，且设置属性级约束、元组级约束和表级约束。
create table yanshi_student(
	sno char(20),
	sname char(20),
	age int null,
	sex char(2) default '男',
	dno char(4) not null,
	birthday datetime null,
	primary key(sno),
	check (sex in ('男''','女')),
	CHECK(SNO LIKE '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
	/*SNO 只能为 8 位数字，且不能以 0 开头*/ ,
	/*定义了元组中 SNO 和 DNO 两个属性值之间的约束条件，SNO 的第 5 位只能为
		1，2，3，且当第 5 位为 1 时，DNO 只能为 0001，第 5 位为 2 时，DNO 只能为 0002，第 5
		位为 3 时，DNO 只能为 0003*/
	CHECK((SUBSTRING(SNO,5,1)='3' AND DNO='0003') OR (SUBSTRING(SNO,5,1) ='2' AND DNO='0002') OR (SUBSTRING(SNO,5,1) = '1' AND DNO='0001')) 
)
--无法执行，sno为8位且只能是数字
INSERT yanshi_student(SNO,SNAME,DNO) values('x0001','li','0001')
INSERT yanshi_student(SNO,SNAME,DNO) values('00001','li','0001')
INSERT yanshi_student(SNO,SNAME,DNO) values('10001','li','0001')

INSERT yanshi_student(SNO,SNAME,DNO) values('20073001','li','0001')
INSERT yanshi_student(SNO,SNAME,DNO) values('20073001','li','0003')

--在 XSGL 数据库中，创建一个规则，限定绑定列的输入值在 0.5 和 5 之间，并使用存储过程 sp_bindrule 将其绑定到 course 表中的 CREDIT 列中，限定学分只能在 0.5和 5 之间。
create rule rule1
as @c1 between 0.5 and 5
exec sp_bindrule 'rule1','course.credit'

--解除 rule1 规则到 yanshi_course.CREDIT 的绑定，并删除该规则。
EXEC sp_unbindrule 'yanshi_course.CREDIT'
DROP RULE rule1

--（1） 创建一个表 yanshi_depart，包含 DNO、DNAME、DADDRESS 和 DEAN 四个属性，DNO 由 4 个数字字符构成，且为该表的主码，DNAME 由 30 个字符构成，DADDRESS 由5 个字符构成，DEAN 由 10 个字符构成。
create table yanshi_depart(
	dno char(4),
	dname char(30),
	daddress char(5),
	dean char(10),
	primary key(dno)
)

--（2） 为例 1 中的 yanshi_student 表建立外键“DNO”，参考表 yanshi_depart 的“DNO”列。
create table yanshi_student(
	sno char(20),
	sname char(20),
	age int null,
	sex char(2) default '男',
	dno char(4) not null,
	birthday datetime null,
	primary key(sno),
	foreign key(dno) references yanshi_depart(dno),
	check (sex in ('男''','女')),
	CHECK(SNO LIKE '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)

--（3） 为表 yanshi_depart 的 DNAME 建立一个规则：@dname IN ('机电学院','信息学院','工商学院')。
create rule rule2
as 
@dname in ('机电学院','信息学院','工商学院')
exec sp_bindrule 'rule2', 'yanshi_depart.dname'

--（4） 为 表 yanshi_depart 的 DADDRESS 建 立 一 个 规 则 ： @daddress LIKE'[A-Z][1-9][1-9][1-9][1-9]'，限定 DADDRESS 的值只能由字母开头，后跟 4 个数字。
create rule rule3
as
@daddress LIKE'[A-Z][1-9][1-9][1-9][1-9]'
exec sp_bindrule 'rule3','yanshi_depart.daddress'

--（5） 删除第 3 小题所建立的规则。
exec sp_unbindrule 'yanshi_depart.daddress'
drop rule rule3