一、建表:

create table student
(
sno char(9) primary key,
sname char(2) unique,
ssex char(2),
sage char(2),
sdept char(20)
);
alter table student modify(sname char(20));

create table course
(
cno char(4) primary key,
cname char(40) not null,
cpno char(4) references course(cno),
ccredit smallint
);
create table sc
(
sno char(9) references student(sno),
cno char(4),
grade smallint,
primary key(sno,cno),
foreign key(cno) references course(cno)
);

insert into student values('201215121', '李勇', '男', '20', 'CS');
insert into student values('201215122', '刘晨', '女', '19', 'CS');
insert into student values('201215123', '王敏', '女', '18', 'MA');
insert into student values('201215125', '张立', '男', '19', 'IS');
insert into student values('201215126', '欧阳风', '男', '20', 'ES');

select * from student;
INSERT INTO course VALUES ('5', '数据结构', '7', '4');
INSERT INTO course VALUES ('1', '数据库', '5', '4');
INSERT INTO course VALUES ('2', '数学', null, '2');
INSERT INTO course VALUES ('3', '信息系统', '1', '4');
INSERT INTO course VALUES ('4', '操作系统', '6', '3');
INSERT INTO course VALUES ('6', '数据处理', null, '2');
INSERT INTO course VALUES ('7', 'PASCAL语言', '6', '4');
INSERT INTO course VALUES ('8', 'DB\\_Design', '3', '5');

select * from course;
INSERT INTO sc VALUES ('201215121', '1', '92');
INSERT INTO sc VALUES ('201215121', '2', '85');
INSERT INTO sc VALUES ('201215121', '3', '88');
INSERT INTO sc VALUES ('201215122', '2', '90');
INSERT INTO sc VALUES ('201215122', '3', '80');

二、约束

1、---修改course表上的在cno字段上定义的外键约束，要求实现级联删除
 

alter table sc add constraint fk_cno foreign key(cno) references course(cno) on delete 

例如：------删除cno=2的课程
delete from course where cno=2; 

可以看到在sc表中和course表中cno为2的被同时删除了

2、-------禁用check约束
 

alter table student disable constraint ck_sage;

3、-------启用check约束

alter table student enable constraint ck_sage;

4、-------删除check约束
 

alter table student drop constraint ck_sage;

2021.5.14