ora-002298 parent keys not found

[dragon99]

Hi, all
I disable the constraints on the columns of the table, and import data into the table, but when I try to enable the disabled constraints, I get the ora-002298 - parent keys not found on some of the columns. Can anybody help me with the script to find out the parent keys of the columns? Thanks in advance.

[TerryD]

Sounds like you're trying to enable a foreign key, but it can't find it's parent key.

Make sure that the Primary Key that the foreign key is based on is enabled. If it is, make sure that there is a record in the Primary key for which the Foreign key is referencing.

Terry

[TerryD]

Must read posts more carefully!

Script -

select a.*
from dba_cons_columns a, dba_constraints b
where b.constraint_name = 'CONSTRAINT_TO_ENABLE'
and a.constraint_name = b.r_constraint_name
order by table_name, position

This will list the key/columns it is referencing (I think - haven't tested since erm.. no foreign keys on my system at the moment)

[sysdba]

You can know which table is child table, if
you get ora-2298 error. You can use
following to get who is/are parent table(s):

col CHILD_TABLE for a18
col PARENT_TABLE for a18
col FK for a18
col PK_ON_MASTER_TABLE for a18

select c.TABLE_NAME CHILD_TABLE,
c.CONSTRAINT_NAME FK,
p.TABLE_NAME PARENT_TABLE,
p.CONSTRAINT_NAME PK_ON_MASTER_TABLE
from user_constraints c, user_constraints p
where c.CONSTRAINT_TYPE = 'R'
and c.R_CONSTRAINT_NAME = p.CONSTRAINT_NAME
and c.TABLE_NAME like upper('&table_name')
/

[dragon99]

Thanks very much, TerryD.
Yes, I am sure the PKs are enabled. But there are some tables are still empty. Does it mean that if the table which FK is dependent on is emtpy, the FK can not be enabled? Do I have to wait until the table is populated and enable it?
Thanks again.

[carp]

That is exactly what must happen. If there are no rows in the parent table, the foreign key cannot be enabled if there are values in the child column. You must populate the parent table first, THEN try to enable the FK constraint.