CREATE TABLE my_table
(
column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constrint_fk
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (col_1, col_2, ... col_n)
);
ALTER TABLE my_table ADD (
CONSTRAINT constrint_fk
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (col_1, col_2, ... col_n)
enable validate);
Here, my_table is the child table to the foreign key constrint_fk. Columns (col_1, col_2, ... col_n) shall be the primary key or an unique index of the parent_table. Columns (column1, column2, ... column_n) shall be an index of my_table to avoid 'eng: TM' locks in a busy database when the parent_table gets DELETE or UPDATE operations frequently.
When I tried to truncate a table before refreshing it, I got error "ORA-02266: unique/primary keys in table referenced by enabled foreign keys". Below query will identify all foreign keys referenced by child tables:
SELECT DISTINCT a.table_name CHILD_NAME, a.constraint_name F_KEY, a.status, a.last_change
FROM dba_constraints a, dba_constraints b
WHERE a.OWNER = UPPER('<owner>
AND a.r_constraint_name = b.constraint_name
AND a.constraint_type = 'R'
AND b.constraint_type in ('P', 'U')
AND b.table_name = UPPER('<MY_TABLE>
Then, run "alter table owner.CHILD_NAME disable constraint F_KEY;" to disable all constraints (foreign keys). After that, I can truncate table MY_TABLE. But if I want to drop the table, I need to drop the constraints first.
One table can have many foreign keys referencing multiple parent tables. Below query finds all foreign keys that links MY_TABLE to all parent tables:
SELECT c.table_name, c.constraint_name, c.constraint_type, c2.table_name P_table, c2.constraint_name P_constraint
FROM dba_constraints c JOIN dba_constraints c2 ON (c.r_constraint_name = c2.constraint_name)
WHERE c.OWNER = UPPER('<owner>
AND c.table_name =UPPER('<MY_TABLE>
AND c.constraint_TYPE = 'R';
When I delete/update data in table MY_TABLE, I do not need to disable foreign keys to all parent table generated from above query.
