CREATE TABLE "SCOTT"."MID_A_TAB"
( "A" VARCHAR2(20 BYTE),
"B" VARCHAR2(10 BYTE),
"DETPNO" VARCHAR2(10 BYTE)
)TABLESPACE "USERS" ;
CREATE TABLE "SCOTT"."MID_B_TAB"
( "A" VARCHAR2(20 BYTE),
"B" VARCHAR2(10 BYTE),
"DEPTNO" VARCHAR2(10 BYTE)
)TABLESPACE "USERS" ;
--给MID_A_TAB表添加主键
alter table mid_a_tab add constraint a_pk primary key (detpno);
--给MID_B_TAB表添加主键
alter table mid_b_tab add constraint b_pk primary key(a);
--给子表MID_B_TAB添加外键,并且引用主表MID_A_TAB的DETPNO列，并通过on delete cascade指定引用行为是级联删除
alter table mid_b_tab add constraint b_fk foreign key (deptno) references mid_a_tab (detpno) on delete cascade;
--向这样就创建了好子表和主表
向主表添加数据记录
SQL> insert into mid_a_tab(a,b,detpno) values('1','1','10');
已创建 1 行。
已用时间: 00: 00: 00.00
向子表添加数据
SQL> insert into mid_b_tab(a,b,deptno) values('1','2','6');
insert into mid_b_tab values('1','2','6')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.B_PK)
已用时间: 00: 00: 00.00
可见上面的异常信息，那时因为子表插入的deptno的值是6，然而此时我们主表中
detpno列只有一条记录那就是10，所以当子表插入数据时，在父表中不能够找到该引用
列的记录，所以出现异常。
但我们可以这样对子表的数据的进行插入(即：在子表的deptno列插入null,因为我们在建表的时候
并没有对该列进行not null的约束限制)：
SQL> insert into mid_b_tab(a,b,deptno) values('3','2',null);
已创建 1 行。
已用时间: 00: 00: 00.00
现在如果我们把子表mid_b_tab中deptno列加上not null约束。
SQL> alter table mid_b_tab modify deptno not null;
alter table mid_b_tab modify deptno not null
*
第 1 行出现错误:
ORA-02296: 无法启用 (SCOTT.) - 找到空值
已用时间: 00: 00: 00.01
上面又出现异常，这是因为现在mid_b_tab表中有了一条记录，就是我们先前添加的
那条记录。
3，2，null
现在我们要把该表的deptno列进行not null约束限制，所以oracle不让我们这样干。
那我们就只有把该表给delete或truncate掉，然后在修改deptno列为非空。
SQL> delete from mid_b_tab;
已删除2行。
已用时间: 00: 00: 00.01
再次修改子表mid_b_tab表的deptno列为非空。
SQL> alter table mid_b_tab modify deptno not null;
表已更改。
已用时间: 00: 00: 00.01
修改成功！
我们再次插入数据
insert into mid_b_tab(a,b,deptno) values('13','2',null);试试。
SQL> insert into mid_b_tab(a,b,deptno) values('13','2',null);
insert into mid_b_tab(a,b,deptno) values('13','2',null)
*
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SCOTT"."MID_B_TAB"."DEPTNO")
已用时间: 00: 00: 00.00
看见现在oracle不让我们插入空值了。
所以我们在创建子表的外键约束时，该表的引用列必须要进行not null限制，也可以在
该列创建unique，或primary key约束，并且引用列与被引用列的数据类型必须相同。
SQL> insert into mid_b_tab(a,b,deptno) values('13','2','10');
已创建 1 行。
已用时间: 00: 00: 00.01
此时数据插入成功，因为此时插入的10，在主表中的被引用列中已经存在了。
现在我们一系列的操作：
SQL> select * from mid_b_tab ;
A B DE
-------------------- ---------- --
13 2 10
已用时间: 00: 00: 00.00
SQL> select * from mid_a_tab;
A B DE
-------------------- ---------- --
1 1 10
已用时间: 00: 00: 00.00
SQL> delete from mid_a_tab;
已删除 1 行。
已用时间: 00: 00: 00.01
#p#副标题#e#
SQL> select * from mid_b_tab;
未选定行
从上边的操作中可以看出当我们删除了主表中的记录后，子表中相应的记录
也被级联删除掉了。
通过引用行为可以确定如何处理子表中的外键字段。引用类型包括3中类型：
1.on delete cascade;--级联删除
2.on set null;--删除主表中的记录后，子表中的相应记录的列被设置为null(但子表的该字段必须支持null值)。
3.on no action;--不允许删除主表中被引用的数据，该操作会被禁止。
如果有on delete cascade，而且没有在子表上加索引：那么每删除主表中的一行
都会对子表做一个全表扫描。而且如果从父表删除多行，那么每从父表中删除一行
就要扫描一次子表。
SQL> select * from mid_a_tab a,mid_b_tab b where
2 a.detpno=b.deptno;
A B DETPNO A B DEPTNO
-------------------- ---------- ---------- -------------------- ---------- ----------
1 2 12 2 2 12
1 2 12 1 1 12
2 22 13 22 212 13
3 33 14 55 6666 14
已用时间: 00: 00: 00.00
执行计划
-------------------------------
关键词标签：oracle主键和外键
相关阅读
热门文章
Oracle中使用alter table来增加,删除,修改列
oracle中使用SQL语句修改字段类型-oracle修
使用低权限Oracle数据库账户得到管理员权限
Oracle对user的访问控制
人气排行 ORACLE SQL 判断字符串是否为数字的语句Oracle中使用alter table来增加,删除,修改列的语法ORACLE和SQL语法区别归纳(1)oracle grant 授权语句如何加速Oracle大批量数据处理Oracle删除表的几种方法ORACLE修改IP地址后如何能够使用Oracle 10g创建表空间和用户并指定权限
查看所有0条评论>>