Error Creating Foreign Key in Oracle

Question

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?

• ypercubeᵀᴹ: What does this query return? SELECT prefix, suffix FROM options MINUS SELECT prefix, suffix FROM product ;
• skylaneffz: Return 0 rows....
• ypercubeᵀᴹ: And is the table products or product? You use one name in the data, above, and another name in the FK definition.
• skylaneffz: BTW...this is Oracle XE if that makes a difference....
• Philᵀᴹ: 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

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