Re: ORA-02298 When create FK refering to a Unique Constraint

On 9 Apr 2006 20:14:21 -0700, xtanto_at_hotmail.com wrote:

>Hi All,
>
>I get error when trying to create Foreign Key to a Table, not referring
>to a PK But to a UNIQUE Constraint.
>
>I can create FK event not refering to PK as long as that table has
>UNIQUE constraint on that specific column, can't I ?
>
>CREATE TABLE INVOICE
>(
> NODOKUMEN VARCHAR2(30 BYTE) NOT NULL,
> COMPID VARCHAR2(5 BYTE) NOT NULL,
> ...
> FAKTURPAJAK VARCHAR2(30 BYTE),
> UPDATETIME DATE
>)
>
>ALTER TABLE INVOICE ADD (
> CONSTRAINT INVOICE_PK PRIMARY KEY (NODOKUMEN) USING INDEX ...
>
>ALTER TABLE INVOICE ADD (
> CONSTRAINT INVOICE_U01 UNIQUE (FAKTURPAJAK) USING INDEX ..
>
>CREATE TABLE PAYMENT
>(
> FAKTURNO VARCHAR2(30 BYTE) NOT NULL,
> .....
> UPDATETIME DATE )
>
>ALTER TABLE PAYMENT ADD (
> CONSTRAINT PAYMENT_PK PRIMARY KEY (FAKTURNO) USING INDEX .....
>
>ALTER TABLE PAYMENT
> ADD CONSTRAINT PAYMENT_R01 FOREIGN KEY (FAKTURNO)
> REFERENCES INVOICE (FAKTURPAJAK);
>
>ERROR at line 2:
>ORA-02298: cannot validate (PAYMENT_R01) - parent keys not found
>
>How can I solve this problem ?
>
>Thank you,
>xtanto

Are you showing everything? Are those tables really empty?

2298 means you have payment records for which you have no invoice records.
You either need to solve *that* problem
or create the constraint in enable novalidate mode, resolve the problem and alter the constraint.

--
Sybrand Bakker, Senior Oracle DBA