oracle主键和外键

主键和外键

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
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

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=4 Bytes=208
          )

   1    0   HASH JOIN (Cost=7 Card=4 Bytes=208)
   2    1     TABLE ACCESS (FULL) OF 'MID_A_TAB' (TABLE) (Cost=3 Card=
          3 Bytes=78)

   3    1     TABLE ACCESS (FULL) OF 'MID_B_TAB' (TABLE) (Cost=3 Card=
          4 Bytes=104)





统计信息
----------------------------------------------------------
        210  recursive calls
          0  db block gets
         74  consistent gets
          0  physical reads
          0  redo size
        748  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL> create index mid_b_index on mid_b_tab(deptno);

索引已创建。

已用时间:  00: 00: 00.00
SQL> select * from mid_a_tab a,mid_b_tab b where
  2   a.detpno=b.deptno;

A                    B          DETPNO     A                    B          DE
-------------------- ---------- ---------- -------------------- ---------- --
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.01

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=4 Bytes=208
          )

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'MID_B_TAB' (TABLE) (Cost
          =1 Card=1 Bytes=26)

   2    1     NESTED LOOPS (Cost=6 Card=4 Bytes=208)
   3    2       TABLE ACCESS (FULL) OF 'MID_A_TAB' (TABLE) (Cost=3 Car
          d=3 Bytes=78)

   4    2       INDEX (RANGE SCAN) OF 'MID_B_INDEX' (INDEX) (Cost=0 Ca
          rd=4)





统计信息
----------------------------------------------------------
         10  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
        748  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          4  rows processed

大家可以从上面的执行计划中看出，向子表添加索引前后查询的差别。如果两表中的数据量再大点
的话那么效果可能会更明显。