Here is an email I received during the weekend.
“Hi Pinal,
I am a senior tester in the leading organization and we have two different environments 1) Testing 2) Production.
As a part of the testing we want to insert garbage data into the database system and see how the application behaves in this scenario. However, there is a small problem. Everytime when I try to insert garbage data in the database system the tables start giving me error that due to constraints on the table, I need to populate data in certain order and it has to be correct. Actually, we want to populate the bad data and see how application reacts to it. Upon talking to development team regarding this they suggested that we should skip this test as due to contraints there will be never bad data. Though, I understand their viewpoint, I must document how the application behaves when there are bad data and data integrity is compromised.
Is there any way I can disable all the Constraint temporarily and load the random data, test my system and later delete all the inserted data and enable the Constraint back?”
This is indeed a great question, I often come across this question again and again. Here is a quick script I have written in my early career which I still use it when I need to do something similar.
-- Disable all the constraint in database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
-- Enable all the constraint in database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
Remember above script when executed right away enable or disable constraints so be extremely careful to execute on production server.
There is one more thing, when you have disabled the constraint, you can delete the data from the table but if you attempt to truncate the table, it will still give you an error. If you need to truncate the table you will have to actually drop all the constraints. Do you use similar script in your environment? If yes, please leave a comment along with the script and I will post it on blog with due credit.
Reference: Pinal Dave (https://blog.sqlauthority.com)
18 Comments. Leave new
There is a script on TechNet for generating drop and create statements for foreign keys. https://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx
The below script can be used to drop and recreate the Foreign Key Constraints on a database.
In this script we are using a temporary tables to select the existing foreign keys and the respective column name and table name. Then we determine the primary key table and column name and accordingly drop and recreate them. I am sure there would be many other ways to do this too, but this script would do it in one instance.
SET NOCOUNT ON
DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = ‘FOREIGN KEY’
UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
–SELECT * FROM @table
–DROP CONSTRAINT:
SELECT
‘
ALTER TABLE [‘ + ForeignKeyConstraintTableSchema + ‘].[‘ + ForeignKeyConstraintTableName + ‘]
DROP CONSTRAINT ‘ + ForeignKeyConstraintName + ‘
GO’
FROM
@table
–ADD CONSTRAINT:
SELECT
‘
ALTER TABLE [‘ + ForeignKeyConstraintTableSchema + ‘].[‘ + ForeignKeyConstraintTableName + ‘]
ADD CONSTRAINT ‘ + ForeignKeyConstraintName + ‘ FOREIGN KEY(‘ + ForeignKeyConstraintColumnName + ‘) REFERENCES [‘ + PrimaryKeyConstraintTableSchema + ‘].[‘ + PrimaryKeyConstraintTableName + ‘](‘ + PrimaryKeyConstraintColumnName + ‘)
GO’
FROM
@table
GO
Thank you
Hi Pinal,
I am suffering from 2months with this problem
Requirement: copying database (without data) and disabling all constraints,after importing the data ,enable the constraints.
how to disable all constraints and how to enable all constraints
couild you help me..!
Hello Pinal,
Thanks for the post and when I execute those statements in my test server I am getting the following error ..The ALTER TABLE statement conflicted with the FOREIGN KEY constraint..may I know the reason?
It seems that Some foreign key constraint is failing while enabling it… You can check for this by
select * from where not in (select from ) .
Hello Pinal ,
The above code for enabling the constraints does not work for me.This works for me
EXEC sp_msforeachtable “ALTER TABLE ? CHECK CONSTRAINT all”
Didn’t worked for me when I executed the script and trying to insert data in to the table it is still rasing me an error
This seems to be my new headache, I simply want to import table data (thru MS Import and Export wizard )blocking the constraints before the process and enable then later.
Can anyone help me out?
You should check the script – https://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx
Sagar, having said that. If you data is inconsistent and failing because of constrains, you might want to check the order in which the ETL is happening or the authenticity / cleanliness of data.
Hello Pinal,
I follow your blog regularly and love all your posts.
I am facing a problem and I can’t find any solution…
My client asked me to create 2 stored procedures – one that drops all the constraints(PK, FK, default values) from the database and one that re-creates them (for each table).
Here is how I create a constraint:
ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [DF_TableName_CreateDate] DEFAULT (getdate()) FOR [CreateDate]
Here is how I drop a constraint:
SELECT @SQL += N’
ALTER TABLE ‘ + OBJECT_NAME(PARENT_OBJECT_ID) + ‘ DROP CONSTRAINT ‘ + OBJECT_NAME(OBJECT_ID) + ‘;’
FROM SYS.OBJECTS
WHERE TYPE_DESC LIKE ‘%CONSTRAINT’ AND OBJECT_NAME(PARENT_OBJECT_ID) = ‘TableName’;
When we execute the stored procedures and run the website at the same time we are getting the following error:
Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Is there a way to create / drop these constraints without locking the tables?
Any help would be appreciated.
thanks so much for this working solution
My pleasure.
Thank you for the article and the scripts. It helped me to improve performance while I delete data from big database. But I can not understand why a delete works better when foreign keys are disabled. Who can explain it for me?
because it has to validate data from FK tables. Query plan would give you your answer.
Thank you for the answer. This is weird for me because I disable constraints of table on which the table depends. I am trying to simplify my case to this example:
create table Grand (grand_id int primary key)
create table Parent (parent_id int primary key, parent_grand_id int)
alter table Parent add constraint FK_Parent_parent_grand_id FOREIGN KEY(parent_grand_id) REFERENCES Grand(grand_id)
create table Child (child_id int primary key, child_parent_id int)
alter table Child add constraint FK_Child_parent_id_Child FOREIGN KEY(child_parent_id) REFERENCES Parent(parent_id)
insert into Grand values (1)
insert into Parent values (1, 1), (2, 1)
ALTER TABLE Parent NOCHECK CONSTRAINT ALL
delete from Parent
ALTER TABLE Parent WITH CHECK CHECK CONSTRAINT ALL
insert into Parent values (1, 1), (2, 1)
delete from Parent
As you can see I don’t disable constraints of Child table
Dear Pinal,
What about “ON DELETE CASCADE” syntax?
The proposed script does not take such options into account and could lead the innocent accidental dba to an irreversible situation…
Please for your comment.
Regards,
Konstantinos