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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment