Oracle 外键 说明

 

 

一. 外键说明

1.1 官网上有关说明如下：

      Maintaining Data Integrity in Application Development

       http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_constraints.htm#sthref748

 

Managing FOREIGN KEY Integrity Constraints

       General information about defining, enabling, disabling, and dropping all types of integrity constraints is given in section "Dropping Integrity Constraints". The present section supplements this information, focusing specifically on issues regarding FOREIGN KEY integrity constraints, which enforce relationships between columns in different tables.

Note:

       FOREIGN KEY integrity constraints cannot be enabled if the constraint of the referenced primary or unique key is not present or not enabled.

       -- 当外键参考的主键不可用时，对应的外键也不可用。

 

Datatypes and Names for Foreign Key Columns

       You must use the same datatype for corresponding columns in the dependent and referenced tables. The column names do not need to match.

       -- 外键及其参考的字段名称可以不一样，但datatype 必须一致。

 

Limit on Columns in Composite Foreign Keys

       Because foreign keys reference primary and unique keys of the parent table, and PRIMARY KEY and UNIQUE key constraints are enforced using indexes, composite foreign keys are limited to 32 columns.

 

Foreign Key References Primary Key by Default

       If the column list is not included in the REFERENCES option when defining a FOREIGN KEY constraint (single column or composite), then Oracle Database assumes that you intend to reference the primary key of the specified table.

-- 当创建外键时，没有指定参考的字段，默认使用primary key

       Alternatively, you can explicitly specify the column(s) to reference in the parent table within parentheses. Oracle Database automatically checks to verify that this column list references a primary or unique key of the parent table. If it does not, then an informative error is returned.

       -- 外键必须是primary key 或者是unique key。

 

 

Privileges Required to Create FOREIGN KEY Integrity Constraints

       To create a FOREIGN KEY constraint, the creator of the constraint must have privileged access to the parent and child tables.

       （1）The Parent Table The creator of the referential integrity constraint must own the parent table or have REFERENCES object privileges on the columns that constitute the parent key of the parent table.

       （2）The Child Table The creator of the referential integrity constraint must have the ability to create tables (that is, the CREATE TABLE or CREATE ANY TABLE system privilege) or the ability to alter the child table (that is, the ALTER object privilege for the child table or the ALTER ANY TABLE system privilege).

 

       In both cases, necessary privileges cannot be obtained through a role; they must be explicitly granted to the creator of the constraint.

 

These restrictions allow:

       （1）The owner of the child table to explicitly decide which constraints are enforced and which other users can create constraints

       （2）The owner of the parent table to explicitly decide if foreign keys can depend on the primary and unique keys in her tables。

 

 

Choosing How Foreign Keys Enforce Referential Integrity

       Oracle Database allows different types of referential integrity actions to be enforced, as specified with the definition of a FOREIGN KEY constraint:

（1）Prevent Delete or Update of Parent Key

       The default setting prevents the deletion or update of a parent key if there is a row in the child table that references the key. For example:

       SQL>create table emp_tab ( foreign key (deptno) references dept_tab);

 

（2）Delete Child Rows When Parent Key Deleted

       The ON DELETE CASCADE action allows parent key data that is referenced from the child table to be deleted, but not updated. When data in the parent key is deleted, all rows in the child table that depend on the deleted parent key values are also deleted. To specify this referential action, include the ON DELETE CASCADE option in the definition of the FOREIGN KEY constraint. For example:

SQL>CREATE TABLE Emp_tab (

    FOREIGN KEY (Deptno) REFERENCES Dept_tab 

        ON DELETE CASCADE);

 

（3）Set Foreign Keys to Null When Parent Key Deleted

       The ON DELETE SET NULL action allows data that references the parent key to be deleted, but not updated. When referenced data in the parent key is deleted, all rows in the child table that depend on those parent key values have their foreign keys set to null. To specify this referential action, include the ON DELETE SET NULL option in the definition of the FOREIGN KEY constraint. For example:

SQL>CREATE TABLE Emp_tab (

       FOREIGN KEY (Deptno) REFERENCES Dept_tab 

     ON DELETE SET NULL);

 

 

Viewing Definitions of Integrity Constraints

       The data dictionary contains the following views that relate to integrity constraints:

ALL_CONSTRAINTS

ALL_CONS_COLUMNS

USER_CONSTRAINTS

USER_CONS_COLUMNS

DBA_CONSTRAINTS

DBA_CONS_COLUMNS

 

 

For example：

SQL>SELECT Constraint_name, Constraint_type, Table_name, R_constraint_name

    FROM User_constraints;

 

 

1.2 外键索引

       如果在对应的child table 上，没有建立外键索引，那么可能造成enq: TM contention 的等待事件。  关于该等待事件具体参考我的Blog：

       Oracle enq: TX contention 和 enq: TM contention 等待事件说明

       http://blog.csdn.net/tianlesoftware/archive/2011/06/04/6526238.aspx

 

Tom 同学提供的一个查询没有见外键索引的表的SQL:

 

/* Formatted on 2011/6/4 15:39:24 (QP5 v5.163.1008.3004) */

SELECT table_name,

       constraint_name,

          cname1

       || NVL2 (cname2, ',' || cname2, NULL)

       || NVL2 (cname3, ',' || cname3, NULL)

       || NVL2 (cname4, ',' || cname4, NULL)

       || NVL2 (cname5, ',' || cname5, NULL)

       || NVL2 (cname6, ',' || cname6, NULL)

       || NVL2 (cname7, ',' || cname7, NULL)

       || NVL2 (cname8, ',' || cname8, NULL)

          columns

  FROM (  SELECT b.table_name,

                 b.constraint_name,

                 MAX (DECODE (position, 1, column_name, NULL)) cname1,

                 MAX (DECODE (position, 2, column_name, NULL)) cname2,

                 MAX (DECODE (position, 3, column_name, NULL)) cname3,

                 MAX (DECODE (position, 4, column_name, NULL)) cname4,

                 MAX (DECODE (position, 5, column_name, NULL)) cname5,

                 MAX (DECODE (position, 6, column_name, NULL)) cname6,

                 MAX (DECODE (position, 7, column_name, NULL)) cname7,

                 MAX (DECODE (position, 8, column_name, NULL)) cname8,

                 COUNT (*) col_cnt

            FROM (SELECT SUBSTR (table_name, 1, 30) table_name,

                         SUBSTR (constraint_name, 1, 30) constraint_name,

                         SUBSTR (column_name, 1, 30) column_name,

                         position

                    FROM user_cons_columns) a,

                 user_constraints b

           WHERE a.constraint_name = b.constraint_name

                 AND b.constraint_type = 'R'

        GROUP BY b.table_name, b.constraint_name) cons

 WHERE col_cnt >

          ALL (  SELECT COUNT (*)

                   FROM user_ind_columns i

                  WHERE i.table_name = cons.table_name

                        AND i.column_name IN

                               (cname1,

                                cname2,

                                cname3,

                                cname4,

                                cname5,

                                cname6,

                                cname7,

                                cname8)

                        AND i.column_position <= cons.col_cnt

               GROUP BY i.index_name)

/

 

 

 

可以通过该SQL 查看没有建外键索引的表。

 

 

1.3  外键的一些示例

-- 创建parent table

SQL> create table parent_tab as select distinct object_type from dba_objects;

Table created.

 

-- 创建child table

SQL> create table child_tab

  2  as

  3  select object_id, object_type, object_name

  4  from all_objects;

Table created.

 

-- parent table添加primary key

 

SQL> alter table parent_tab add constraint pk_parent_tab primary key (object_type);

Table altered.

 

-- child table 创建 primary key

SQL> alter table child_tab add constraint pk_child_tab primary key (object_id);

Table altered.

 

--child table 创建 on delete cascde 外键

SQL> alter table child_tab add constraint fk_child_parent_tab foreign key (object_type) references parent_tab on delete cascade;

Table altered.

 

--查看约束信息

SQL> select constraint_name, constraint_type, table_name, r_constraint_name from user_constraints where table_name in ('PARENT_TAB','CHILD_TAB');

 

 

constraint_name   constraint_type  table_name           r_constraint_name

------------------------- --------------- -------------------- -----------------

SYS_C0019826              C    CHILD_TAB

SYS_C0019827              C    CHILD_TAB

PK_CHILD_TAB             P    CHILD_TAB

FK_CHILD_PARENT_TAB    R    CHILD_TAB        PK_PARENT_TAB

PK_PARENT_TAB           P     PARENT_TAB

 

其中约束类型对应如下：

C - Check constraint on a table

P - Primary key

U - Unique key

R - Referential integrity

V - With check option, on a view

O - With read only, on a view

H - Hash expression

F - Constraint that involves a REF column

S - Supplemental logging

 

其中有2个为C 的约束，这个不是我们建的，我们看以下他们的内容：

SQL>  select table_name,constraint_name,search_condition, generated from user_constraints where table_name in ('parent_tab','child_tab');

 

table_name     constraint_name     search_condition          generated

-------------------- ------------------------- ------------------------- --------------------

child_tab     sys_c0019826      "object_id" is not null     generated name

child_tab     sys_c0019827      "object_name" is not null  generated name

child_tab     pk_child_tab                              user name

child_tab     fk_child_parent_tab                        user name

parent_tab    pk_parent_tab                             user name

 

这2个是系统自动生成的。

 

--测试： 从parent table 删除数据，看child table是否cascade 删除了

SQL> select count(*) from parent_tab;

  COUNT(*)

----------

        40

SQL> select count(*) from child_tab;

 

  COUNT(*)

----------

     43573

 

--从parent table 删除后，child table的数据也删除了

SQL> delete from parent_tab;

40 rows deleted.

 

SQL> select count(*) from child_tab;

  COUNT(*)

----------

         0

SQL> commit;

 

Commit complete.

 

-- 删除table

--直接drop parent table，报错ora-02449错误

SQL> drop table parent_tab;

drop table parent_tab

           *

ERROR at line 1:

ORA-02449: unique/primary keys in table referenced by foreign keys

 

-- 先drop child table 后，在drop parent table，正常

SQL> drop table child_tab;

Table dropped.

SQL> drop table parent_tab;

Table dropped.

 

 

二.  有关外键的一些讨论

 

       外键在维护数据的一致性和完整性有它的作用。 itpub 上有一个帖子，是讨论需不需要使用外键。

 

帖子链接如下：

       http://www.itpub.net/thread-1313696-1-1.html

 

我的观点：事事没有绝对，存在既有它的价值，适当的使用还是有必要的。

 

 

 

 

 

-------------------------------------------------------------------------------------------------------

QQ:492913789

Email:ahdba@qq.com

Blog: http://www.cndba.cn/dave

DBA1 群：62697716(满);   DBA2 群：62697977(满)   DBA3 群：62697850(满)  

DBA 超级群：63306533(满);  DBA4 群： 83829929  DBA5群： 142216823   

DBA6 群：158654907  聊天 群：40132017   聊天2群：69087192

--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请