Oracle Primary FOREIGN Key Management

Using a primary foreign KEY constraint makes the data integrity.

1, query all the constraints on the table

SELECT * FROM User_constraints t
where t.table_name= ' FATHER ';

2. Querying tables with primary foreign key relationships

Select C.owner,c.constraint_name,c.constraint_type,c.table_name,f.owner,f.constraint_name,f.constraint_type, F.table_name
From dba_constraints C, dba_constraints F
where C.r_owner=f.owner
and C.r_constraint_name=f.constraint_name
and c.table_name= ' child '; --Querying all parent tables for child tables

3. The record inserted in the child table must exist in the parent table, otherwise it will be reported to parent key not found

sql> INSERT into child values (' Datong ', 1);
INSERT into child values (' Datong ', 1)
*
ERROR at line 1:
Ora-02291:integrity constraint (SCOTT. FK_ADDR) violated-parent key not found

4, the parent table's records can not be found in the child table to be deleted, otherwise it will be reported to the children record found
Sql> Delete from father where id=1;
Delete from father where id=1
*
ERROR at line 1:
Ora-02292:integrity constraint (SCOTT. fk_id) Violated-child Record found

Sql> Delete from father where id=2;

1 row deleted.

Sql> commit;

Commit complete.

5, how to completely delete the parent table data, such as TRUNCATE, drop

Sql> truncate TABLE father;
TRUNCATE TABLE father
*
ERROR at line 1:
ora-02266:unique/primary keys in table referenced by enabled foreign keys

For the above scenario, you can disable the reference constraint for all the child tables of the Father table, using the following SQL to get the Disabled child table constraint statement:

Select ' ALTER TABLE ' | | c.owner| | '. ' | | c.table_name| | ' Modify constraint ' | | c.constraint_name| | ' Disable; ' "Exec_sql"
From user_constraints C, user_constraints F
where C.r_owner=f.owner
and C.r_constraint_name=f.constraint_name
and F.table_name= ' FATHER ';

Exec_sql

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

ALTER TABLE SCOTT. Child Modify constraint fk_id disable;

Then execute the above query results, you can ban all the child table constraints, truncate the parent table will not be an error.

Sql> ALTER TABLE SCOTT. Child Modify constraint fk_id disable;

Table altered.

Sql> truncate TABLE father;

Table truncated.

Of course, the reference constraint of the child table does not necessarily get up (enable), depending on whether the child table has data.

Sql> ALTER TABLE SCOTT. Child modify constraint fk_id enable;
ALTER TABLE SCOTT. Child Modify constraint fk_id enable
*
ERROR at line 1:
Ora-02298:cannot Validate (SCOTT. FK_ID)-Parent keys not found

If you delete all the child table data, you can get a reference constraint on the (Enable) child table.

sql> TRUNCATE TABLE child;

Table truncated.

Sql> ALTER TABLE SCOTT. Child modify constraint fk_id enable;

Table altered.

sql> drop table father;
drop table Father
*
ERROR at line 1:
ora-02449:unique/primary keys in table referenced by foreign keys

In this case, you can use the CASCADE constraints clause to delete the reference constraint for the child table.

sql> drop TABLE Father cascade constraints;

Table dropped.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle Primary FOREIGN Key Management