1

I have the problem similar to this one SQL Server foreign key conflict in a multi values statement? However, in sql server 2008.

While I am reading data from csv file, there is some id already not exist in parent and thus return this error:

INSERT statement conflicted with the FOREIGN KEY constraint

May I know if there is a way similar to MySQL insert ignore. Such that I can simply skip the problematic data.

I accept that if there is no method other than creating a stored procedure with a new temp table (insert into a table without foreign key first, and then re-insert with where foreign_id exists in (select id from parent)).

As I really cannot find any in documentation, asking for ensuring I didn't miss anything.

MT-FreeHK
  • 2,462
  • 1
  • 13
  • 29

1 Answers1

2

One general solution which comes to mind would be to temporarily turn off the foreign key constraints, and do the insert. Then, afterwards, you may run a cleanup script/query to remove or rectify child records which are pointing to parents which do not exist. Once your data is in good shape, then turn on the foreign key constraints again.

Read How can foreign key constraints be temporarily disabled using T-SQL? to learn how to disable/enable a foreign key constraint for a single table:

ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint            -- disable
ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint   -- enable
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I see, but it just make me feel like foreign key is quite useless in sql-server 2008? I can't even try catch it. – MT-FreeHK Aug 24 '18 at 07:51
  • They're not useless at all. The problem is your data, which isn't in a good state. If you generally expected your data to not have problems, then you could leave the constraints in place. Then, if an error happened, you'd go back and cleanup your data, rather than disabling the constraints. – Tim Biegeleisen Aug 24 '18 at 07:51