Wednesday, December 19, 2007

Foreign Key Constraint Error ORA-02298

While trying to create FK on cust_status got the following error.

ALTER TABLE CUST_STATUS ADD (CONSTRAINT CUST_STATUS_FK FOREIGN KEY (LOGIN) REFERENCES USERS(LOGIN))
*
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.CUST_STATUS_FK) - parent keys not found

SQL> desc cust_status
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTID NOT NULL NUMBER(8)
TIMESTAMP NOT NULL DATE
STATUS NOT NULL VARCHAR2(35)
LOGIN VARCHAR2(20) ID NUMBER

SQL> desc users
Name Null? Type
----------------------------------------- -------- ----------------------------
LOGIN NOT NULL VARCHAR2(20) PASSWORD VARCHAR2(30)
LAST_NAME VARCHAR2(50)
FIRST_NAME VARCHAR2(50)
EMAIL VARCHAR2(100)
ACTIVE CHAR(1)

To find out which rows are the problem rows,

SQL> select custid,login from cust_status a
where not exists (select 'x' from users where login = a.login);

You can delete the data using...

SQL> delete from cust_status
where login is null or login not in ( select login from users )

Now you should be able to create the FK constraint.

No comments: