Dealing with FK constraints - Emptying and refilling sql server 2008 db

I have a SQL DB in MS SQL Server 2008 R2

For development purposes, I am trying to clear all the data - then add in my dummy data.

After some struggles with FK Constraints, and using

ALTER TABLE ? NOCHECK CONSTRAINT ALL
DELETE FROM ?
ALTER TABLE ? CHECK CONSTRAINT ALL

I managed to clear the data.

Now I want to add some dummy data.

Lets say we have 3 tables, Country, Address and Country_Address (linking address to country).

I have added data to Country and Address

But when I try to add to Country_Address:

No row was updated.

The data in row 1 was not committed. Error Source: .Net SqlClient Data Provider. Error Message: The INSERT statement conflicted with the FOREIGN KEY constraint

I am not quite sure why this is happening, because all I am doing is linking the newly added Country and Addresses - which both exist - so why is it conflicting with FK constraint?

From googling it has hinted that reseeding the tables may be r开发者_高级运维equired to fix this. Firstly i'm not 100% sure what reseeding means, I am assuming it is talking about resetting the autogenerated ID column.

I did notice when adding new records to Country or Address that they use int incremented from last record (which is now gone), e.g. start from id of 400

How can I add the data in?

It sounds like you have an ID column in the Country and Address tables, and the values in Country_Address are the ID values that reference the other two tables. And, it appears that you already have data for Country_Address that has the original values for the referenced data.

So, reseeding may be the solution. I assume that your tables look something like this.

Country
-------
CountryID int identity(1, 1)
Country varchar(100)

Address
-------
AddressID int identity(1, 1)
Address varchar(100)
City varchar(100)

It doesn't matter exactly what columns exist for reseeding, except for the CountryID and AddressID columns in each table. The identity(1, 1) property indicates autonumbering starting at 1 and incrementing by 1. So, to reseed the Country and Address tables, you can do the following before adding data to the Country and Address tables:

DBCC CHECKIDENT ('Country', RESEED, 1)
DBCC CHECKIDENT ('Address', RESEED, 1)

It probably isn't necessary to run the command for Country_Address, unless it has a column with an identity property.

Reseeding means resetting the auto-incrementing ID's for the primary keys. You can reseed a SQL Server table via Management Studio or using DDL statements. Example

Remember that if what you're doing is testing you can always just drop the foreign key constraints entirely to get them out of your way. Just make sure that the data you load into the tables populates the foreign keys correctly.

If you are going to empty all the tables, I find it easiest to script the database structure (you have done this and put it into source control right?) and then drop the datbase and recrete it. Then run the script to add the dummy data.

Try truncating the tables TRUNCATE TABLE table_name and then reinserting the data.