【数据库实验】完整性约束

create table student(sno varchar(20),
                     sname varchar(20),
                     sgender varchar(10),
                     sage int,
                     sdept varchar(20))

1
2
3
4
5
6

insert into student values('1222','张丽','女',21,'计算机'),
					      ('1223','刘立','男',25,'数学'),
                          ('1224','王晓华','女',22,'计算机'),
                  	      ('1225','李佳','男',23,'计算机'),
                          ('1226','张月','女',22,'建筑')
1
2
3
4
5

select *
from student
1
2

create table course(cno varchar(20),
					cname varchar(20),
					cpno varchar(20),
                    credit int)
1
2
3
4

insert into course values('0001','数据库','0002',4),
                         ('0002','数据结构','0003',4),
                         ('0003','C语言',null,4),
                         ('0004','数学',null,4),
                         ('0005','生物学导论',null,4),
                         ('0006','计算机网络',null,4),
                         ('0007','网络安全',null,4)
1
2
3
4
5
6
7

select *
from course
1
2

create table sc(sno varchar(20),
			    cno varchar(20),
				grade int)
1
2
3

insert into sc values('1222','0001',95),
                    ('1222','0002',80),
                    ('1222','0003',92),
					('1222','0004',90),
                    ('1223','0004',85),
                    ('1223','0002',Null),
                    ('1224','0001',93),
					('1225','0001',87),
                    ('1226','0005',Null),
				    ('1224','0002',95)
1
2
3
4
5
6
7
8
9
10

select *
from sc
1
2

一、主键约束

（1）给student增加主键约束

alter table student
add constraint pk primary key(sno)
1
2

（2）给course表设置主码

alter table course
add constraint pkc primary key(cno)
1
2

（3）给sc表设置主码

alter table sc
add constraint psc primary key(sno,cno)
1
2

（4）在student插入一条记录

insert into student values('1227','李娜','女',20,'数学')
1

（5）再次执行

insert into student values('1227','李娜','女',20,'数学')
1

结论：出错原因是主键重复

二、外键约束

（1）在sc表中插入没有的学生，如果成功插入，证明没有建立外键约束（这个系统极不稳定）

insert into sc values('001','005',100)
1

（2）删除刚才插入的记录

delete from sc
where sno='001'
1
2

（3）在sc表中创建了外键约束，被参照关系为student

alter table sc
add constraint fk foreign key(sno) references student(sno)
on delete cascade
on update cascade
1
2
3
4

drop table sc：删除sc表
1

（4）测试外键约束

insert into sc values('001','005',100)

insert into sc values('1223','005',100)

select *
from sc

1
2
3
4
5
6
7

(5)测试级联（删除）

select *
from student

select *
from sc

delete from student
where sno='1223'
1
2
3
4
5
6
7
8

(6)测试级联更新

update student
set sno='1224a'
where sno='1224'
1
2
3

(7) 测试在参照关系中插入

select *
from student

select *
from sc

insert into sc values('1225','0004',95)
insert into sc values('1280','0004',95)
1
2
3
4
5
6
7
8

结论： 如果插入的数据中外键的取值超出了被参照关系主键的取值，则拒绝

（8）测试参照关系中修改

update sc 
set sno = '1280'
where  sno = '1225'
1
2
3

结论： 如果修改的数据中外键的取值超出了被参照关系主键的取值，则拒绝

（9）测试被参照关系中修改数据

select *
from student

select *
from sc

update student
set sno ='1224'
where sno = '1224a'
1
2
3
4
5
6
7
8
9

结论：在被参照关系中修改数据，参照关系中相应的数据会级联修改

（10）在被参照关系中删除

delete from student 
where sno = '1224'

select *
from student

select *
from sc
1
2
3
4
5
6
7
8

结论：在被参照关系中删除数据，参照关系中相应的数据会级联删除

（11）sc表当中插入一条选课记录（此时外键还未建立）

insert into sc values('1226','0001',90)

select *
from sc

insert into sc values('1226','AAAA',90)  [违反了外键约束]
1
2
3
4
5
6

(12)在course表中删除一条记录（此时未建立外键约束）

delete from course
where cno='0001'

select *
from course

select *
from sc
1
2
3
4
5
6
7
8

(13)在sc表中删除以下记录

delete from sc
where cno='AAAA'
select *
from sc
1
2
3
4

(14)在sc表中删除选择了0001号课程的信息，因为其在course表中删除了

delete from sc
where cno = '0001'

select *
from sc
1
2
3
4
5

(15)为course表和sc表建立外键约束

   alter table sc
   add constraint fc foreign key(cno)  references course(cno)
   on delete cascade
   on update cascade
1
2
3
4

（16）sc表中插入一条选课记录，（此时建立了外键约束）

insert into sc values（'1226','AAAA',90）
1

发现拒绝插入
1

(17)在course表中修改课程号

update course
set cno ='0002a'
where cno ='0002'

select *
from course

select *
from sc
1
2
3
4
5
6
7
8
9

结论：在被参照完整关系中修改，实现了参照关系的级联修改

（18）在course表中删除

delete from course 
where cno = '0002a'

select *
from course

select *
from sc
1
2
3
4
5
6
7
8

结论：在被参照关系删除，实现了参照中的级联删除

三、用户自定义约束

（1）为student 表建立用户自定义约束

BEGIN
declare mesg varchar(10);
if new.sgender not in ('男','女')  then
     set mesg='error ssex';
     signal sqlstate 'HY000' set message_text=mesg;
END if;
1
2
3
4
5
6

（2）在student表中插入一条记录

insert into student values('1230','网络','女',25,'建筑')

select *
from student

insert into student values('1231','网络','未知',25,'建筑')
1
2
3
4
5
6

（3）为sc表建立用户自定义约束

BEGIN   
declare mesg varchar(100);
if new.grade not between 0 and 100 then
  set mesg="error grade";
    signal sqlstate 'HY000' set message_text=mesg;
end if;
1
2
3
4
5
6

（4）在sc表中插入一条记录

insert into  sc values('1227','0001',90)

select *
from sc

insert into  sc values('1227','0002',900)
1
2
3
4
5
6