Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 2 tables:

PRODUCTS

PREFIX      SUFFIX      ATTR1   ATTR2
-----------------------------------------------------
001         AAA         50      100
001         BBB         20      300
010         ACC         50      500
100         ABC         10      200
100         CBA         30      200
100         BBB         10      100

OPTIONS

PREFIX      SUFFIX      OPT_CODE    
----------------------------------------
001         AAA         A1
001         AAA         A2
001         AAA         A3
001         BBB         A2
001         BBB         A1
001         BBB         A3
100         ABC         A1
100         CBA         B1
100         BBB         A2

Created with this DDL:

create table products (
    prefix VARCHAR2(10),
    suffix VARCHAR2(10),
    attr1  VARCHAR2(100),
    attr2  VARCHAR2(100)
    )

create table options (
    prefix VARCHAR2(10),
    suffix VARCHAR2(10),
    opt_code VARCHAR2(2)
    )

I have created a primary key in the products table:

alter table products add constraint products_pk primary key (prefix, suffix);

and a primary key in the options table:

alter table options add constraint options_pk primary key (prefix, suffix, opt_code);

When I try and add a foreign key to the options table like so:

ALTER TABLE options ADD CONSTRAINT fk_options_products FOREIGN KEY (prefix, suffix)
   REFERENCES products (prefix, suffix);

I am getting this error:

SQL Error: ORA-02298: cannot validate (PRODDB.FK_OPTIONS_PRODUCTS) - parent keys not found
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause:    an alter table validating constraint failed because the table has
           child records.
*Action:   Obvious

I validated that all the child records (in OPTIONS) have entries in the parent table (PRODUCTS) but not all the entries in PRODUCTS have child entries in OPTIONS.

My understanding of that error - you get that when you don't have a parent record for a child entry.

Am I missing something here?

share|improve this question
3  
What does this query return? SELECT prefix, suffix FROM options MINUS SELECT prefix, suffix FROM product ; –  ypercube Jun 20 at 19:42
    
Return 0 rows.... –  skylaneffz Jun 20 at 19:45
    
And is the table products or product? You use one name in the data, above, and another name in the FK definition. –  ypercube Jun 20 at 19:46
    
BTW...this is Oracle XE if that makes a difference.... –  skylaneffz Jun 20 at 19:46
2  
I just noticed the PRODDB schema name in the error message. Is that the user you're logged in as, or is there a public synonym for the object? If so, explains the error –  Phil Jun 21 at 9:38

1 Answer 1

We can debug this. Create your FK with this code below, which includes the EXCEPTIONS clause. It will identify the specific rows in your child table options responsible for the errors.

CREATE TABLE fk_exceptions (
    row_id     ROWID
  , owner      VARCHAR2(30)
  , table_name VARCHAR2(30)
  , constraint VARCHAR2(30)
);

ALTER TABLE options
  ADD CONSTRAINT fk_options_products
  FOREIGN KEY (prefix, suffix)
  REFERENCES products (prefix, suffix)
  EXCEPTIONS INTO fk_exceptions;

You'll still get your ORA-02298, but the evidence will be harvested and deposited into fk_exceptions table. Your culprit row(s) will be revealed by:

SELECT * FROM options WHERE rowid IN (SELECT row_id FROM fk_exceptions);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.