Search
 
SCRIPT & CODE EXAMPLE
 

SQL

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

PREVIOUS NEXT
Code Example
Sql :: sql server sleep 
Sql :: sql server get schema of table 
Sql :: oracle auto increment primary key 
Sql :: mysql docker image for macbook m1 
Sql :: install mysql workbench ubuntu 20.04 
Sql :: mysql url 
Sql :: spring datasource properties mysql 
Sql :: foreign key mysql 
Sql :: sqlite to csv statement 
Sql :: how to rename a database in tsql 
Sql :: load sql into mamp 
Sql :: mac install mysql 
Sql :: install mysql on bash 
Sql :: Assign value to var in SQL 
Sql :: mysql row_number() example 
Sql :: show all tables postgres 
Sql :: sql manhattan distance 
Sql :: rename table column postgresql 
Sql :: current date sql 
Sql :: nested if in mysql 
Sql :: sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unable to open database file 
Sql :: sql current timestamp table 
Sql :: select last 30 days sql 
Sql :: postgresql check privileges on schema 
Sql :: sql insert multiple rows 
Sql :: mysql connection w3 
Sql :: kill session inactive oracle 
Sql :: sql list dates between two dates 
Sql :: how to count null values in sql 
Sql :: update join sql 
ADD CONTENT
Topic
Content
Source link
Name
8+9 =