CODEWITHSUNDEEP
sqlssms
Ivan
Ivan

Reputation: 503

Disable constraints temporary in SQL server

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

Upvotes: 2

Views: 2827

Answers (1)

Jahan Zinedine
Jahan Zinedine

Reputation: 14874

-- 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

Upvotes: 3

Related Questions