how do I drop constraints from a table 2

[shanghai2004]

Dear Expert,

I exported data from one database and need to import to another database (all tables are empty) using BCP utility.
But some tables have foreign keys and it doesn't let me import. How do I drop those constraints and then put them on when import data is done?

Thanks

 

[mrdenny]

You can use the DROP CONSTRAINT command to drop the foreign keys.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[shanghai2004]

Dear Expert,

the DROP CONSTRAINT command can help me to import data to a table that has foreign key. But how do I get the constraint back to the table? Can I save constraints before I drop them?

Sorry, I should do more research on it, but I run out of time. The dead line is 21th of Dec.

Thanks

 

[SQLBI]

Surely you're going to have problems when the CONSTRAINTS are re-applied as your FK is checking for a parent record in the PK table.

IMHO, you need to look at your process and break down your INSERT so that you import to the PK table first, then the FK table.

Assuming you are confident that you have each FK in the PK table.

I recently had a simimilar issue with Client and Patient data. Each patient has a 1:M relationship with a client, defined by a FK constraint. By importing the Clients first and then the patients, i removed the need to drop and recreate the FK constraint.

I hope this makes sense.

Cheers,
Leigh

Sure, if it has a microchip in it, it must be IT... Now what seems to be the problem with your toaster...?

 

[SQLDenis]

you can also use alter table with nocheck and check later on

ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap

do your stuff here


ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mrdenny]

If you need to drop the constraint to load data then you need to look at your loading process. As LeighMoore suggested you should be loading the parent table first with any new/updated records then loading the child table with it's new/updated records.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com