CODEWITHSUNDEEP
sqldatabasepostgresql
crytodactyl
crytodactyl

Reputation: 71

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

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?

Upvotes: 0

Views: 1420

Answers (2)

Massimo Petrus
Massimo Petrus

Reputation: 1891

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 ?

  1. Insert all ph. numbers including the ones without users

  2. 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

Upvotes: 0

Bohemian
Bohemian

Reputation: 425448

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

Upvotes: 2

Related Questions