-
Hi,
when I do the following, I get the ora-2298
SQL> alter table INLINEAD enable constraint FKINLINEAD01;
alter table INLINEAD enable constraint FKINLINEAD01
*
ERROR at line 1:
ORA-02298: cannot validate (HUBV2.FKINLINEAD01) - parent keys not found
How do I find out the parent keys for the parent table? Please help. Thanks.
-
SELECT r_constraint_name
FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_NAME='FKINLINEAD01';
-
Thanks, Maachan,
I did according to your sql statement, and find the constraint name of the parent table and I enabled it. And then run the query in my first post, but it still gave the same error message. Why is that?
The following is what I did:
SQL> SELECT r_constraint_name
2 FROM DBA_CONSTRAINTS
WHERE CONSTRAINT_NAME='FKINLINEAD01';
R_CONSTRAINT_NAME
------------------------------
PKADAGENCY
PKADAGENCY
SQL> alter table adagency enable constraint pkadagency;
Table altered.
-
ORA error is not complaining that it can't find the PK or UK of the parent table which your FK references. It complains because you have records in your child table that have no coresponding values in the parent table to reference.
For example, you have a DEPARTMENT table (parent table) with departments 10, 20 and 30. You also have EMPLOYEE table (child table) with employee name and department number they work in. In EMPLOYEE table you have some people with DEPTNO=10, some with DEPTNO=20, some with DEPTNO=30, but you also have entered one employee with DEPTNO=40. Now you try to enforce foreign key on EMPLOYEE.DEPTNO which references DEPARTMENT.DEPTNO. Oracle will not be able to enforce this FK because you have no coresponding row in EMPLOYEE for employee with DEPTNO=40 and will return you ORA-2298.
If you wan't to find rows from your EMPLOYEE table that has no coresponding row in DEPARTMENT you would go with something like this:
SELECT * FROM employee WHERE NOT EXISTS
(SELECT 1 FROM department WHERE department.deptno=employee.deptno);
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Thanks very much for your detailed explaination, Jmodic.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|