19 Oracle外键约束
--外键
--作用：当在字表中插入的数据在父表中不存在，则会自动报错
--概念：当一张表的某个字段需要依赖另一个表的某个字段的值，则使用外键。其中主动依赖的表称为字表，被依赖的表称为父表。外键加在字表中。
--使用：
--在创建表时字段后：cno number(10) references 父表名(父表字段名)
--在创建表的最后：constraints fk_表名_字段名 foreign key (字段名) references 父表名(父表字段名)
--在已经创建表完毕后添加：alter table 表名 add constraints fk_表名_字段名 foreign key (字段名) references 父表名(父表字段名)
--缺点：
--当子表没有清除完毕，无法删除父表中的记录,除非使用级联删除
delete from clazz where cno=1;--报错：已找到字表记录
--解决方法：
--使用级联删除：当删除父表数据时，自动删除此字段和子表相关的所有记录 或 设置子表中的依赖字段为空
- --在字表创建时：cno number(10) references clazz(cno) on delete cascade 删除父表记录时会删除字表相关记录 --在创建子表时：cno number(10) references clazz(cno) on delete set null 这样删除父表记录时，字表相关记录的相关字段会被设置为空，字表记录本身不会被删除
- --注意：字表的依赖字段不能设置为非空，否则on delete set null无效
例子：
create table student(
sno number(10) primary key,
sname varchar2(10) not null,
sage number(3) check(sage>0 and sage <150),
ssex char(4)check(ssex='男' or ssex='女'),
sfav varchar2(500),
sqq varchar2(30) unique,
cno number(10)
)
--查询
select * from student;
select * from clazz;
--添加学生测试数据
insert into student values(1,'张三001',18,'男','唱歌','112414545',1);
insert into student values(2,'张三002',18,'男','唱歌','112414546',1);
insert into student values(3,'李四001',18,'男','唱歌','112414547',2);
insert into student values(4,'李四002',18,'男','唱歌','112414548',2);
--创建班级表
create table clazz(
cno number(10) primary key,
cname varchar2(100) not null,
cdesc varchar2(300)
)
--添加班级测试数据
insert into clazz values(1,'java高薪就业班','好厉害');
insert into clazz values(2,'python就业班','真的凶');
--联合查询：学生及其班级信息
select * from student
join clazz
on student.cno=clazz.cno;
--发现问题：可以插入一个学生但这个学生的班级是一个不存在的班级
insert into student values(5,'王二',19,'男','打篮球',213123,3);
--解决方法：在创建学生表时，cno(班级number)设置为另一个表的主键，这个行为称为设置外键
--先删掉原有的student表
drop table student;
--在创建新的设置了外键的student表，执行后再执行上面的添加学生的语句
create table student(
sno number(10) primary key,
sname varchar2(10) not null,
sage number(3) check(sage>0 and sage <150),
ssex char(4)check(ssex='男' or ssex='女'),
sfav varchar2(500),
sqq varchar2(30) unique,
cno number(10) references clazz(cno)--或在最后 foreign key(字段) references 另一个表名(字段名)
)
--外键概念
--作用：当在字表中插入的数据在父表中不存在，则会自动报错
--概念：当一张表的某个字段需要依赖另一个表的某个字段的值，则使用外键。
--其中主动依赖的表称为字表，被依赖的表称为父表。外键加在字表中
--使用：
--在创建表时字段后：cno number(10) references 父表名(父表字段名)
--在创建表的最后：constraints fk_表名_字段名 foreign key (字段名) references 父表名(父表字段名)
--在已经创建表完毕后添加：alter table 表名 add constraints fk_表名_字段名 foreign key (字段名) references 父表名(父表字段名)
--缺点：
--当子表没有清除完毕，无法删除父表中的记录,除非使用级联删除
delete from clazz where cno=1;
--级联删除：当删除父表数据时，自动删除此字段和子表相关的所有记录
--使用方式,创建字表时设置外键后面添加 on delete cascade：在字表创建时：cno number(10) references clazz(cno) on delete cascade
--先删掉原来的约束再添加
select * from user_cons_columns order by table_name;
alter table student drop constraints SYS_C007041;
alter table student add constraints fk_student_cno foreign key(cno) references clazz(cno) on delete cascade;
--成功删除，我们来删父表记录试试
delete from clazz where cno=1;
--查询:相关自己录被一并删除
select * from student;
select * from clazz;
--缺点：使用上面的方式删除父表记录时会删除字表相关记录
--解决方法，在创建子表时在设置外键后面添加 on delete set null,这样删除父表记录时，字表相关记录的相关字段会被设置为空，记录本身不会被删除
--注意：字表的依赖字段不能设置为非空，否则on delete set null无效
--先删掉原来的约束
alter table student drop constraints fk_student_cno;
--再添加带delete set null关键字的约束
--删除学生：delete from student;
delete from student;
--添加约束
alter table student add constraints fk_student_cno foreign key(cno) references clazz(cno) on delete set null;
--添加班级
--添加学生
insert into student values(1,'张三001',18,'男','唱歌','112414545',1);
insert into student values(2,'张三002',18,'男','唱歌','112414546',1);
insert into student values(3,'李四001',18,'男','唱歌','112414547',2);
insert into student values(4,'李四002',18,'男','唱歌','112414548',2);
--删除clazz记录
delete from clazz;
--查询学生
select * from student;
