/SQL/Mssql disable foreign key constraint

Mssql disable foreign key constraint

If you want to disable all constraints in the database just run this code:

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
To switch them back on, run: (the print is optional of course and it is just listing the tables)

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
I find it useful when populating data from one database to another. It is much better approach than dropping constraints. As you mentioned it comes handy when dropping all the data in the database and repopulating it (say in test environment).

If you are deleting all the data you may find this solution to be helpful.

Also sometimes it is handy to disable all triggers as well, you can see the complete solution here.

Similar codes

8 votes189 views4 answers
Convert date to dd/mm/yyyy sql
SQL
7 votes141 views3 answers
How to check table name in current database sql
SQL
0 vote154 views1 answer
Download sql file of countries names
SQL
-1 vote149 views1 answer
Insert results of a stored procedure into a temporary table
SQL
0 vote211 views1 answer
Mysql get nested records
SQL
0 vote103 views1 answer
Get all stored procedures in sql server
SQL
2 votes156 views1 answer
Postgresql create schema in specific database
SQL
1 vote148 views1 answer
Md5 encryption for existing records
SQL
4 votes139 views1 answer
Check database name postgresql
SQL
1 vote152 views1 answer
Postgres convert exisiting column to text
SQL