实验 5 数据的完整性管理

实验 5   数据的完整性管理

一、实验目的

• 掌握实体完整性的实现方法。
• 掌握用户定义完整性的实现方法。
• 掌握参照完整性的方法。

二、实验内容

• 数据库的完整性设置。

三、实验步骤

• 可视化界面的操作方法：

• 实体完整性
  1. 将 student 表的“sno”字段设为主键：在表设计界面中，单击左边的行选定块，

选定“sno”字段，单击工具按钮 设置主键。如图 1 所示：

 

图 1

1. 1. 将“sc”表的“sno”和“cno”设置为主键：在表设计界面中，单击并按住 Ctrl键拖动左边的行选定块，选定 sno 和 cno 字段，单击工具按钮设置主键。如图2 所示：

 

 

图 2

• 用户定义完整性

将“ssex”字段设置为只能取“男”，“女”两值：在表设计界面，点击鼠标右键

——CHECK 约束——添加约束，添加 CK_student_sex 名称，然后在约束表达式框中输入“ssex in ('男','女')”。如图 3 所示。

图 3

• 参照完整性

将“sc”表中的“sno”字段设为外键，参照“student”表中的“sno”字段：打开

“sc”表的设计界面，点击工具栏按钮    或点击鼠标右键——“关系”菜单项，在弹

出的属性(properties)对话框中点击“新建”按钮，新建一个名为“FK_SC_Student”的

“关系”，在其属性中点击“表和列规范”右侧的按钮，即：

 

打开“表和列”对话框，在“主键表(Primary key table)”下拉框中选择“student” 表，在其下的字段选择框中选择“sno”，在“外键表(Foreign key table)”下拉框中选择 “sc”表，在其下的字段选择框中选择“sno”，单击关闭即可。见图 4。

 

图 4

类似方法再定义一个名为“FK_SC_Course”的“关系”，该关系中，“sc”表中的

“cno”字段设为外键，参照“Course”表中的“cno”字段。

 

 

• SQL 命令方式操作方法：
  • 实体完整性

1. 将“student”表的“sno”字段设为主键： 当“student”表已存在则执行：

alter table student

add constraint pk_sno primary key (sno)

当“student”表不存在则执行:

Create table student(

sno CHAR(5) primary key, sname CHAR(10) NOT NULL,

ssex CHAR(2), sage int,

sdept CHAR(4))

注:可用命令“drop table student”删除“student”表

1. 添加一身份证号字段，设置其惟一性。(注：操作前应删除表中的所有记录)

Alter table student

add id char(18) unique (id)

1. 将“sc”表的“sno”和“cno”设置为主键： 当“sc”表已存在则执行：

alter table sc add constraint PK_SnoCno primary key (sno,cno)

当“sc”表不存在则执行：

Create table sc( sno CHAR(5), cno CHAR(2),

gradeINT NULL,

constraint PK_SnoCno primary key (sno,cno)

)

• 用户定义完整性

1. 将“ssex”字段设置为只能取“男”，“女”两值：当“student”表已存在则执行：

alter table student

add constraint CK_Sex check (ssex in ('男' ,'女'))

当“student”表不存在则执行：

Create table student(

sno CHAR(5) primary key, sname CHAR(10),

ssex    CHAR(2) check (ssex in ('男' ,'女')),

sage int,

sdept CHAR(4), id char(18)

)

1. 设置学号字段只能输入数字: alter table student

add constraint CK_Sno_Format check (sno like '[0-9][0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]')

注意：如果表中已有数据不符合以上要求，执行之前要先删除 student 表已有数据。

1. 设置身份证号的输入格式: alter table student

add constraint CK_ID_Format check ((id like

'[0-9][0-9][0-9][0-9][0-9][0-9][1-2][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]_') OR

(id like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]'))

1. 设置 18 位身份证号的第 7 位到第 10 位为合法的年份(1900-2050) alter table student

add constraint CK_ID_Format2 check (len(id)=18 and ( (convert(smallint,substring(id,7,4) )>=1900)

and(convert(smallint,substring(id,7,4) )<=2050)) )

1. 设置男生的年龄必须大于 22, 女生的年龄必须大于 20.

Alter table student

add constraint CK_age check (ssex='男' and sage>=22 or ssex='女' and sage>=20 )

• 参照完整性

1.建立“sc”表中的“sno”字段为外键，参照“student”表中的“sno”字段：

当“sc”表已存在则执行：

alter table sc

add constraint FP_sno foreign key (sno) references student(sno)

当“sc”表不存在则执行：

Create table sc(

sno CHAR(5) constraint FP_sno foreign key references student(sno), cno CHAR(2),

grade INT NULL,

constraint PK_SnoCno primary key (sno,cno)

)

• 完整性验证

1. 实体完整性：在“student”表数据浏览可视化界面中输入学号相同的两条记录， 将会出现错误，拷屏记录下出错界面。

或者在命令窗口输入下面两条命令也会出现错误提示，拷屏记录下出错界面。

insert into student values('95001','张三','男',24,'CS','42222919901012903X') insert into student values('95001','李四','女',21,'CS','422229199510129031')

下面的语句用来验证“sc”表中的实体完整性，拷屏记录下出错界面。

insert into sc values('95002', '10',65) insert into sc values('95002', '10',90)

1. 用户定义完整性:

使用下面的语句验证“ssex”字段的域完整性，拷屏记录下出错界面。

insert into student values('95009','张匀','大',20,'CS', ‘42222919901012904X’)

 

1. 参照完整性：

使用下面的语句“验证”sc 表中的“sno”字段的域完整性(假设 student 表中没有学号

为“95998”的学生记录)，拷屏记录下出错界面。

insert into sc values('98998', '10',98)

 

四、实验任务:

1、建立课程表的实体完整性,和选课表中课程号 cno 的参照完整性;

2、练习建立表的主外键约束、唯一约束、取空值约束、用户自定义的约束；

3、建立 SC 表的分数字段限定在 0-100 之间。