SQL Management Studio Insert statement confliction

Question

I'm working on creating a development database for my boss. I have the SQL script that creates all the tables, but now I have loads of data to import. There are 5 tables total in the database, and their ID's are related to eachother like so:

Note: I'm not allowed to share info like database/table names

Table1
+-- Table2
    +-- Table3
        +-- Table4
            +-- Table5

Meaning that Table 5 has a field that points to a Table 4's ID, and so on.

So I'm doing a bunch of inserts like this:

INSERT INTO [dbo].[Table2] (Table1_ID, AnotherField) VALUES (159268, 408659)

But it throws this exception for each one:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TABLE2_TABLE1". The conflict occurred in database "--removed by author--", table "dbo.TABLE1", column 'TABLE1_ID'.

EDIT: So the problem was that I needed to insert the data in Table1 first. So my Boss found some data that will suffice for Table1... So Problem solved :)

Answer 1

1. You should do a top-down import: first import all records from Table1, then Table2, etc.
2. Or, to make things easier, and have a faster experience, you could disable all your constraints before executing, and re-enable them after completion. Please, take a look here: http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-tsql

EDIT:

Well, if you don't have Table1 data all you can do is remove all foreign-key constraints that reference Table1 (probably from Table2). Or you could try generating "fake" Table1 data, considering all distinct Table1 IDs available on Table2 (aaarrgh).

Answer 2

There isn't really much to add - the error message is pretty clear.

You are trying to insert an ID value in to Table2 however the that ID value doesn't exist in Table1. Hence the error message.

You need to insert the data in to Table1 before you insert in to Table2

EDIT:

If there is no data for Table1 then the database constraints are poorly thought out. You will have to remove the constraint from Table2.

You will have to ask them for the data for Table1 then.

Answer 3

You can temporarily disable the foreign key constraint, do the insert, and then re-enable it using CHECK and NOCHECK.

WITH NOCHECK CONSTRAINT disables the constraint and allows an insert that would normally violate the constraint.

WITH CHECK CONSTRAINT re-enables the constraint.

Here's a detailed explanation of how to do this.