Finding Foreign Key Constraints in Oracle (page 3 of 3)

Let us find out which constraints are enabled on our parent table TEMP_JP1.

SQL> select owner, constraint_name, constraint_type, table_name, r_owner, r_constraint_name

2 from all_constraints where constraint_type in ('P','U') and table_name='TEMP_JP1';

OWNER CONSTRAINT_NAME C TABLE_NAME R_OWNER R_CONSTRAINT_NAME

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

JP TEMP_JP1_PK P TEMP_JP1

JP TEMP_JP1_UK U TEMP_JP1

Now we know the primary/unique key constraints enabled on our parent table, TEMP_JP1.

The r_constraint_name( primary/unique constraint name in the parent table) column in the all_constraints view is referenced by the constraint_name (foreign key(referential integrity) constraint in the child table), when the constraint_type is ‘R'. Using this definition, let us find out all the foreign key(referential integrity) constraints referencing the TEMP_JP1 table:

SQL> select owner, constraint_name, constraint_type, table_name, r_owner, r_constraint_name

2 from all_constraints

3 where constraint_type='R'

4 and r_constraint_name in (select constraint_name from all_constraints

5 where constraint_type in ('P','U') and table_name='TEMP_JP1');

OWNER CONSTRAINT_NAME C TABLE_NAME R_OWNER R_CONSTRAINT_NAME

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

JP TEMP_JP2_FK R TEMP_JP2 JP TEMP_JP1_PK

JP TEMP_JP3_FK R TEMP_JP3 JP TEMP_JP1_UK

Here in my sub-query, I am supplying ONLY the primary and unique key constraint types with the qualifier “where constraint_type in (‘P','U') “, as we had seen earlier, that foreign key constraints can only be enabled on a child table, when a PRIMARY/UNIQUE KEY constraint is enabled on the parent table.

We had identified the foreign key constraints that are referencing the parent table TEMP_JP1.

Let us try to disable the primary/unique key constraints on the table.

SQL> alter table temp_jp1 disable constraint temp_jp1_pk;

alter table temp_jp1 disable constraint temp_jp1_pk

ERROR at line 1:

ORA-02297: cannot disable constraint (JP.TEMP_JP1_PK) - dependencies exist

Let us disable the unique key constraint from our parent table, TEMP_JP1.

SQL> alter table temp_jp1 disable constraint temp_jp1_uk;

alter table temp_jp1 disable constraint temp_jp1_uk

ERROR at line 1:

ORA-02297: cannot disable constraint (JP.TEMP_JP1_UK) - dependencies exist

Now we understand that as long as the foreign key constraints are enabled on the child tables, the data in the parent table is protected.

If we need to manipulate the data in the parent table, TEMP_JP1, first disable the foreign key(referential integrity) constraints on the child tables that are referencing the parent table.

Now let us disable the foreign key(referential integrity) constraints on the child tables, identified from our earlier query.

SQL> alter table temp_jp2 disable constraint temp_jp2_fk;

Table altered.

SQL> alter table temp_jp3 disable constraint temp_jp3_fk;

Table altered.

SQL> truncate table temp_jp1;

Table truncated.

We could successfully truncate the parent table, after disabling the foreign key constraints on the child tables, that were referencing the parent table.

A ready built script to identify and disable/enable foreign key constraints on child tables:

SQL> select 'alter table '||a.owner||'.'||a.table_name||

2 ' disable constraint ' || a.constraint_name ||';'

3 from all_constraints a , all_constraints b

4 where a.constraint_type = 'R'

5 and a.r_constraint_name = b.constraint_name

6 and a.r_owner = b.owner

7 and b.table_name = 'TEMP_JP1';

'ALTERTABLE'||A.OWNER||'.'||A.TABLE_NAME||'DISABLECONSTRAINT'||A.CONSTRAINT_NAME

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

alter table JP.TEMP_JP3 disable constraint TEMP_JP3_FK;

alter table JP.TEMP_JP2 disable constraint TEMP_JP2_FK;

I hope this narrative has given a clear picture about foreign key constraints and how to handle the problems, while manipulating the data in the parent table.

For the experienced:

SQL> INSERT INTO TEMP_JP1 VALUES(2,NULL);

1 row created.

SQL> INSERT INTO TEMP_JP3 VALUES(NULL);

1 row created.

SQL> INSERT INTO TEMP_JP3 VALUES(NULL);

1 row created.

SQL> insert into temp_jp1 values(null,null);

insert into temp_jp1 values(null,null)

ERROR at line 1:

ORA-01400: cannot insert NULL into ("JP"."TEMP_JP1"."COL1")

Please be careful: Both Primary / Unique key indexesdo not allow duplicates in the column. All primary key indexes are unique. A table can have only one Primary key index and can have multiple unique key indexes. One main difference is that you can not insert NULL values into a primary key constraint enabled column, whereas you can insert NULL values into a unique key constraint enabled column.

For this reason, if your parent key in the parent table is indexed with a unique key constraint, then enable NOT NULL constraint on the parent key column.

SQL> alter table temp_jp1 modify(col2 not null);

Table altered.

SQL> insert into temp_jp1 values(2,null);

insert into temp_jp1 values(2,null)

ERROR at line 1:

ORA-01400: cannot insert NULL into ("JP"."TEMP_JP1"."COL2")

JP Vijaykumar

<< BACK

	RELATE LINK :

Home | News | Tutorials | World Wide Article |

Training & Service | Profile | Carrer Skills | Forum

พบปัญหา website , link เสีย ,แลก link,แสดงความเห็น หรือ ติดต่อเป็นสปอนเซอร์สนับสนุน
email : webmaster@oracleskill.com