2

I want to copy all data from one database to another one, but I have many problems with foreign keys which cannot be inserted that easily. I would like to disable foreign keys, insert data, and then enable them again. What is the easiest way to do that?

Thanks, Ivan

Ivan
  • 495
  • 3
  • 9
  • 20

1 Answers1

3
-- disable the check_sale constraint in the employee table
ALTER TABLE employee NOCHECK CONSTRAINT check_sale

-- enable the check_sale constraint in the employee table
ALTER TABLE employee WITH CHECK CHECK CONSTRAINT check_sale

if your script span multiple tables you can retrieve all the constraints and disable all of them.

USE AdventureWorks;
GO
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
GO

http://www.mssqlcity.com/Articles/General/using_constraints.htm

Jahan Zinedine
  • 14,616
  • 5
  • 46
  • 70
  • This will leave them in an untrusted state. You need to ensure to use the `WITH CHECK` option so the query optimiser can use them. c.f. [13 Things You Should Know About Statistics and the Query Optimizer](http://www.simple-talk.com/sql/t-sql-programming/13-things-you-should-know-about-statistics-and-the-query-optimizer/) points 9 and 10. – Martin Smith Jan 04 '11 at 15:26
  • It's obvious that the data would be invalid, he must do clean ups then enable them. – Jahan Zinedine Jan 04 '11 at 15:29
  • 2
    The way you re-enable them is the problem though. More details here http://www.mssqltips.com/tip.asp?tip=1539 – Martin Smith Jan 04 '11 at 15:31