Oracle primary and foreign key management, oracle Key Management

Oracle primary and foreign key management, oracle Key Management

Use the primary and foreign key constraints to ensure data integrity.

1. query all the constraints on the table

Select * from user_constraints t
Where t. table_name = 'father ';

2. query 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 '; -- Query all parent tables corresponding to CHILD

3. The records inserted in the subtable must exist in the parent table. Otherwise, the parent key not found is reported.

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. Records in the parent table can be deleted only when they cannot be found in the subtable. Otherwise, the child record found is reported.
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 data in the parent table, such as truncate and drop

SQL> truncate table father;
Truncate table father
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

 

In the above case, You can first disable the reference constraints of all the sub-tables in the father table and use the following SQL statement to disable the sub-Table constraints:

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 preceding query results to disable all the child table constraints. The truncate parent table will not report errors.

SQL> alter table SCOTT. CHILD modify constraint FK_ID disable;

Table altered.

SQL> truncate table father;

Table truncated.

Of course, at this time, the subtable reference constraints may not be able to (enable), depending on whether the subtable 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

You can delete all the sub-table data to enable the reference constraints of the sub-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 constraints of the sub-table.

SQL> drop table father cascade constraints;

Table dropped.

 

 

 

 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.