ORACLE--案例：对级联关系的使用

--创建学生表
CREATE TABLE student(
       s_id NUMBER(3) CONSTRAINT stu_id_pk PRIMARY KEY,
       s_classname NUMBER(4),
       s_name VARCHAR2(20));

--创建班级表
CREATE TABLE class(
       t_id NUMBER(4) CONSTRAINT cla_id_pk PRIMARY KEY,
       t_classname VARCHAR2(20));

--主键约束：学生表s-classname是外键，班级表t-classname是主键
ALTER TABLE student ADD CONSTRAINT STU_CN_FK FOREIGN KEY(s_classname) REFERENCES class (t_id);
--先有主键，后有外键。向班级表插入数据
INSERT INTO CLASS VALUES(1993,'第五期');
INSERT INTO CLASS VALUES(1994,'第六期');
INSERT INTO CLASS VALUES(1995,'第七期');
--向学生表中插入数据，主键外键的值要一一对应
INSERT INTO student VALUES(1,1993,'coco');
INSERT INTO student VALUES(2,1994,'yige');
INSERT INTO student VALUES(3,1995,'Cxiaoran');

--删除主键班级表数据
--删除失败，违反了约束条件，子表已经引用父级并产生联系，因此在子记录已有的数据不可单独删除父表对应的那个数据
DELETE FROM class WHERE t_id=1993;--失败

--解决方法，先删除子记录中的数据，在删除主键班级表数据
DELETE FROM student WHERE s_classname=1993;
DELETE FROM class WHERE t_id=1993;

--查询学生表
SELECT * FROM student;
--查询班级表
SELECT * FROM class;

（1）级联关系：要先删除学生表已有的外键约束,在添加约束ON DELETE SET NULL;（级联关系约束与外键约束是并列的关系，相互独立存在。）
ALTER TABLE student ADD CONSTRAINT STU_CN_FK1 FOREIGN KEY(s_classname) REFERENCES class (t_id) ON DELETE SET NULL;
--删除主键某一个数据
DELETE FROM class WHERE t_id = 1995;
（2）删除父表数据的同时删除对应子表的数据
ALTER TABLE student ADD CONSTRAINT STU_CN_FK1 FOREIGN KEY(s_classname) REFERENCES class (t_id) ON DELETE CASCADE;
--删除主键某一个数据
DELETE FROM class WHERE t_id = 1994;