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;

<