外键约束
概念
- 一张表的一个字段受限于另外一张表的一个字段对应的值。这里涉及到两张表:被引用的表叫主表(父表),另外一张叫从表(子表)。
- 它们的关系:主从表关系(父子表关系)
- 子表:定义了外键的表
外键的取值要么取父表中字段对应的值,要么取NULL值
严重受限于父表
- 父表:被引用的字段要具有唯一性(绝大多数都是用的父表的主键)
理论上是可以一张表里的一个字段引用另一个字段,但一般都是用两张表
外键的实现
-
建立表:
先建立父表,后建立子表(因为子表要用到父表)
除非先不考虑外键(建完表后再加),表多的时候可以不考虑建表顺序。弊端:对数据要求很严格,一个垃圾数据都不能放
父表:
create table parent( id number primary key, name varchar2(30), );
子表:
create table child( id number primary key, name varchar2(30), fid number constraint child_fid_fk references parent(id), );
-
插入数据:
一般先插入父表数据,再插入子表数据。除非把子表的外键值设置成NULL值,否则会出完整性错误。
insert into child values(1, 'test1', 1);
要改为:先加入父表数据
insert into parent values(1, 'p1'); insert into parent values(2, 'p2'); insert into child values(1, 'test1', 1);
-
删除数据
子表中有关联的数据,需要先删子表,后删父表。
select * from parent;
这里删除父表的id为2的是没关系的,只要没有子表和它关联就行
有关联的必须先删子表的数据,再删父表的数据
delete from parent where id=1;
-
删除表
先删子表,后删父表
除非使用casecade constraints 解除关联
drop table parent;
先删父表会出错:
有一个被foreign keys关联的unique/primary key的字段在表中
使用casecade constraints解除关联就可以删掉父表
drop table parent cascade constranints;
外键的表级约束实现
父表:
create table parent( id number primary key, name varchar2(30), );
子表:
create table child( id number primary key, name varchar2(30), fid number, constraint child_fid_fk foreign key(fid) references parent(id), );
先建立表,后加外键
- 子表s_emp :
里面有外键dept_id
- 父表s_dept
查看脚本summit2_drop.sql发现:1270行
ALTER TABLE s_emp ADD CONSTRAINT s_emp_dept_id_fk FOREIGN KEY (dept_id) REFERENCES s_dept(id);
级联删除和级联置空
- 级联删除:
在外键的最后,加上on delete cascade 就是级联删除
再删除主表数据时和主表关联的子表数据也会删除
- 级联置空
在在外键的最后,加上on delete set null就是级联置空
再删除主表数据时,会把和主表关联的外键设置成NULL
演示:先删表,不管有没有
(1)建立一张父表部门表
- id number primart key
- name varchar2(30)
create table mydept( id nimber constraint mydept_pk primary key, name varchar2(30), );
(2)建立一张子表 员工表
- id number primart key
- name varchar2(30)
- dept_id number 外键(关联到部门表)
- salary varchar2(30)
create table mtemp( id number constraint myepm_id_pk primary key, name varchar2(30), salary number, dept_id number constraint myemp_depy_id_fk reference mydept(id) on delete cascade, );
desc mtemp;
(3)分别向两张表中放入数据
部门表:
insert into mydept values(1, 'test1'); insert into mydept values(2,'test2');
select * from mydept;
员工表:
insert into mtemp values(1, 'empa', 5000,1); insert into mtemp values(2, 'empb', 4500,1); insert into mtemp values(3, 'empc', 5500,1); insert into mtemp values(4, 'empd', 5800,2); insert into mtemp values(5, 'empe', 5100,2);
select * from mtemp;
提交数据:commit;
(4)因为有on delete cascade,所以删除父表中的id为1的部分是可以的,并不会因为有关联而删不掉。
delete from mydept where id=1;
select * from mydept;
select * from mtemp;
(5)如果换为on delete set null,所以删除父表中的id为1的部分是可以的,而子表的关联的相应部分置为空
delete from mydept where id=1;
select * from mydept;
select * from mtemp;
注意:如果是windows写在.sql脚本里,则所有以上的代码包括commit都写入,再在cmd切到存放脚本的磁盘(如D)。
用ftp传到服务器上(192.168.0.26)
- 这里清屏用!cls
- put 要传送的文件名把文件传送到服务器
- @要执行的文件名执行文件
- 在SQL>@/user/openlab/要执行的脚本文件