Error Creating Foreign Key in Oracle

foreign keyoracle

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?

Best Answer

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);
Related Question