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

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

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

select *
from student

create table course(cno varchar(20),
					cname varchar(20),
					cpno varchar(20),
                    credit int)

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)

select *
from course

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

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)

select *
from sc

一、主键约束

（1）给student增加主键约束

alter table student
add constraint pk primary key(sno)

（2）给course表设置主码

alter table course
add constraint pkc primary key(cno)

（3）给sc表设置主码

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

（4）在student插入一条记录

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

（5）再次执行

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

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

二、外键约束

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

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

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

delete from sc
where sno='001'

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

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

drop table sc：删除sc表

（4）测试外键约束

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

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

select *
from sc

(5)测试级联（删除）

select *
from student

select *
from sc

delete from student
where sno='1223'

(6)测试级联更新

update student
set sno='1224a'
where sno='1224'

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

select *
from student

select *
from sc

insert into sc values('1225','0004',95)
insert into sc values('1280','0004',95)

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

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

update sc 
set sno = '1280'
where  sno = '1225'

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

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

select *
from student

select *
from sc

update student
set sno ='1224'
where sno = '1224a'

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

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

delete from student 
where sno = '1224'

select *
from student

select *
from sc

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

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

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

select *
from sc

insert into sc values('1226','AAAA',90)  [违反了外键约束]

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

delete from course
where cno='0001'

select *
from course

select *
from sc

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

delete from sc
where cno='AAAA'
select *
from sc

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

delete from sc
where cno = '0001'

select *
from sc

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

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

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

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

发现拒绝插入

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

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

select *
from course

select *
from sc

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

（18）在course表中删除

delete from course 
where cno = '0002a'

select *
from course

select *
from sc

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

三、用户自定义约束

（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;

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

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

select *
from student

insert into student values('1231','网络','未知',25,'建筑')

（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;

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

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

select *
from sc

insert into  sc values('1227','0002',900)