外键创建的认识.TXT

对于外键的理解，以前只停留在"子表的列依赖于主表的主键列"这样肤浅的认识上。
前几天设计某个表的时候重新看了一下外键的定义，外键的值必须出现在特定表的主键或者唯一键中。

以下是对该问题的一个小实验：

Connected to Oracle Database 10g Release 10.2.0.1.0
Connected as scott

SQL> drop table t1;

Table dropped

SQL> drop table t2;

Table dropped

创建t1、t2，分别作为主表和子表。
SQL> create table t1 as select rownum rn,t.* from dept t;

Table created

SQL> create table t2 as select * from emp;

Table created

SQL> alter table t1 add constraint pk_t1 primary key(rn);

Table altered

SQL> alter table t2 add constraint pk_t2 primary key(empno);

Table altered

SQL> alter table t2 add rn number;

Table altered

SQL> desc t1
Name   Type         Nullable Default Comments
------ ------------ -------- ------- --------
RN     NUMBER                                
DEPTNO NUMBER(2)    Y                        
DNAME  VARCHAR2(14) Y                        
LOC    VARCHAR2(13) Y                        

SQL> desc t2
Name     Type         Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO    NUMBER(4)                             
ENAME    VARCHAR2(10) Y                        
JOB      VARCHAR2(9)  Y                        
MGR      NUMBER(4)    Y                        
HIREDATE DATE         Y                        
SAL      NUMBER(7,2)  Y                        
COMM     NUMBER(7,2)  Y                        
DEPTNO   NUMBER(2)    Y                        
RN       NUMBER       Y                        

SQL> alter table t2 add constraint fk_t1_t2 foreign key(rn) references t1(rn);

Table altered

SQL> alter table t2 add constraint fk_t1_t2_2 foreign key(deptno) references t1(deptno);

alter table t2 add constraint fk_t1_t2_2 foreign key(deptno) references t1(deptno)

ORA-02270: 此列列表的唯一或主键不匹配

SQL> create unique index idx_t1_deptno on t1(deptno);

Index created

SQL> alter table t2 add constraint fk_t1_t2_2 foreign key(deptno) references t1(deptno);

alter table t2 add constraint fk_t1_t2_2 foreign key(deptno) references t1(deptno)

ORA-02270: 此列列表的唯一或主键不匹配

SQL> alter table t1 add constraint unique_deptno unique(deptno);

Table altered

SQL> alter table t2 add constraint fk_t1_t2_2 foreign key(deptno) references t1(deptno);

Table altered

实验比较简单，但改变了我对外键的理解。

1）可以在某个表上创建对另一个表的多个外键约束关系
2）唯一索引和唯一约束是有差别的。创建外键约束，需要被引用表的特定列是主键列（意味着存在主键索引、主键约束），
或者被引用表的特定列上存在唯一性约束，而不是唯一索引。

下面看看存在外键约束的情况下能否删除对应的主键索引、删除主键约束，唯一索引。

SQL> alter table t1 drop constraint pk_t1;

alter table t1 drop constraint pk_t1

ORA-02273: 此唯一/主键已被某些外键引用

SQL> drop index pk_t1;

drop index pk_t1

ORA-02429: 无法删除用于强制唯一/主键的索引

SQL>
SQL>
SQL>  alter table t1 drop constraint unique_deptno;

alter table t1 drop constraint unique_deptno

ORA-02273: 此唯一/主键已被某些外键引用

SQL> drop index idx_t1_deptno;

drop index idx_t1_deptno

ORA-02429: 无法删除用于强制唯一/主键的索引

其实在创建唯一性约束的时候会自动在对应列上创建同名的唯一索引，比如：
SQL> alter table t1 add constraint unique_dname unique(dname);

Table altered

SQL> select index_name from user_indexes where table_name='T1';

INDEX_NAME  
-------------
PK_T1       
IDX_T1_DEPTNO
UNIQUE_DNAME

其删除操作：
SQL> drop index unique_dname;                   
                                                
drop index unique_dname                         
                                                
ORA-02429: 无法删除用于强制唯一/主键的索引      
                                                
SQL> alter table t1 drop constraint unique_dname;
                                                
Table altered                                   

通过对两个语句的10046事件跟踪，对跟踪文件使用tkprof进行格式化，可以看到增加约束比创建索引多做了更新tab$\col$等操作。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-768246/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-768246/