How to insert into a table if it violates a foreign key constraint

Question

I am working on a script that moves data between two databases.

I am moving a table of Phone Numbers. Each Phone Number is for a user.

The problem is that each Phone Number entry references a User with a User ID. Some of these users do not exist anymore, so when I try to insert, it returns a foreign key constraint violation.

insert or update on table "phone_numbers" violates foreign key constraint "fk3843uenfej83jf32wde"
user_id = 10 is not present in table users

However, I can't go and delete each single user reference as there are thousands of references. So what would be the best way to approach it?

Should I simply remove the foreign key constraint?

Yes, you should remove the foreign key constraint if it is not appropriate for your data. Seems weird though that users would disappear. — Gordon Linoff
– Gordon Linoff, Commented Oct 22, 2020 at 15:25
The users can be deleted. However, the phone number list will still reference them which causes the conflict. Is it completely safe to remove the constraint ? — crytodactyl
– crytodactyl, Commented Oct 22, 2020 at 15:27

Bohemian · Accepted Answer · 2020-10-22 15:34:28Z

2

Phone numbers that belong to non existent users are termed “orphaned” data.

Either clean up orphaned data in the source data (orphaned data shouldn’t exist):

delete from phone_number
where not exists (select * from user where id = user_id)

Or don’t select them when exporting:

select p.*
from phone_number p
join user u on u.id = p.user_id

answered Oct 22, 2020 at 15:34

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

Or use soft deletes so the data is not orphaned.

Massimo Petrus · Accepted Answer · 2020-10-22 15:37:33Z

I would not remove the constraint, as it can have impacts on other things (application ? report ? Whatever). So the question is wHhat do you need ?

Insert all ph. numbers including the ones without users
Insert only ph. numbers with users associated

In any case load your data to a 'temp' table call, temp_phones, without any constraint.

In case 1 migrate data to phone_numbers making userid = null if the user is not present anymore. You can do it with an "easy" query

In case 2 migrate data to phone_numbers only when the userid of the record is found in your user table, also this can be done with a query

You can perform both processes also after having migrate the data. In this case you should disable\remove the constraint, update the userid according to the proposed rules, then recreate the constraint

Collectives™ on Stack Overflow

How to insert into a table if it violates a foreign key constraint

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related