Simply adding a foreign key SQL

Question

I'm trying to add a foreign key constraint to my user_info table, that is a primary key in my users table (user_id):

ALTER TABLE user_info
ADD CONSTRAINT fk_userID
FOREIGN KEY (user_id)
REFERENCES users(user_id);

However, I'm getting this error:

Error starting at line 26 in command:
ALTER TABLE user_info
ADD CONSTRAINT fk_userID
FOREIGN KEY (user_id)
REFERENCES users(user_id)
Error report:
SQL Error: ORA-02298: cannot validate (CLAYBANKS.FK_USERID) - 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

Any ideas?

Solution

This error means that you have user_ids in your user_info table that are not present in the users table. The foreign key can therefore not be applied.

You just have to remove all orphaned records (or UPDATE them to null if possible) in the users_info table, or add corresponding entries in the users table. Try to find them before deleting or updating them, to avoid destroying important data.

For example:

DELETE FROM user_info i WHERE user_id NOT IN (SELECT user_id FROM users)

Or

UPDATE user_info SET user_id = NULL WHERE user_id NOT IN (SELECT user_id FROM users)