数据库完整性（级联删除）

第五章——数据库完整性（级联删除）

create table Student
(
Sno char(9) primary key,
Sname char(20) unique,
Ssex char(2),
Sage char(2),
Sdept char(20)
);

create table course
(
Cno char(4) primary key,
Cname char(40) not null,
Cpno char(4), --references course(cno),
Ccredit smallint,

foreign key(Cpno) references course(Cno)
);

create table SC
(
Sno char(9) references Student(Sno),
Cno char(4),
Grade smallint,

primary key(sno,cno)
);

insert into Student values('201215121', '李勇', '男', '20', 'CS');
INSERT INTO student VALUES ('201215122', '刘晨', '女', '25', 'CS');
INSERT INTO course VALUES ('2', '数学', null, '2');
INSERT INTO course VALUES ('6', '数据处理', null, '2');
INSERT INTO sc VALUES ('201215121', '2', '85');
INSERT INTO sc VALUES ('201215122', '2', '90');

select * from student;
select * from course;
select * from sc;

--drop table Student;
--drop table course;
--drop table SC;
--purge recyclebin;

--为student表添加约束ck_age，保证输入的年龄在20-35之间；
alter table student add constraint ck_age check(sage >= 20 and sage <= 35);

--修改cs表上的cno字段上定义的外键约束，要求实现级联删除；
alter table sc add constraint fk_cno foreign key(cno) references course(cno) on delete cascade;
delete from course where cno='2';

--禁用student表上的ck_age，并验证禁用效果
alter table student disable constraint ck_age;

--启用student表上的ck_age，并验启用效果
alter table student enable constraint ck_age;

--删除student表上的ck_age，比较和禁用的区别
alter table student drop constraint ck_age;
INSERT INTO student VALUES ('201215123', '王敏', '女', '18', 'MA');
delete from student where sname='王敏';