SQL Server confirms this, and the row has been inserted. To disable a foreign key in a SQL Server database, you can use a simple ALTER statement such as:ĪLTER TABLE tableReferencing NOCHECK CONSTRAINT FK_Referencesįollowing this statement on can insert the following row that would violate the referential integrity: Table 'dbo.tableReferenced', column 'colA'. The conflict occurred in database 'FKplay', The INSERT statement conflicted with the FOREIGN KEY constraint SQL Server, as expected, reacts with the following error message: This insert should fail, because there is no corresponding row in tableReferenced.

INSERT INTO tableReferencing VALUES ( 1000 ) The foreign key is established, and one can check this by inserting a row that would violate referential integrity: This will create the two tables and populates them like this: INSERT INTO tableReferencing VALUES ( 2 ) INSERT INTO tableReferencing VALUES ( 1 ) INSERT INTO tableReferenced DEFAULT VALUES ( colA INT NOT NULL PRIMARY KEY IDENTITY(1, 1) To create and populate the two tables run: As the name suggests the tableReferencing will reference the tableReferenced table. The two tables are tableReferenced and tableReferencing. I will use a simple two table example to demonstrate how to enable and disable foreign keys, and how to detect inconsistencies in the database. And, if we have millions of rows of data, and we are confident that they are consistent, disabling foreign keys is the best option.Īfter the large data batch is loaded, the database should once again enforce its consistency rules, and the foreign keys should be restored to their original states. But foreign key relationships can be complex, and sometimes even the order in which data rows are added to a single table is significant. For example if there is a foreign key set up on a table referencingTable and a referencedTable, one should start loading data for the referencedTable first. It is easier to load data on a per table basis, and if the foreign keys are disabled, one can load the table data for each table.

Why would one ever disable a foreign key?Īn example for the need to temporarily disable foreign keys is when one wants to load a large batch of consistent data into a set of tables that reference each other via foreign keys.

So, in this article, I will examine some rarely explored areas that concern foreign keys in particular, I will look at disabled and un-trusted foreign keys.

I have seen some confusion as to how one re-enables these disabled constraints in such a way that the database’s consistency remains unaffected. However, sometimes it is necessary to disable them temporarily. Foreign Keys and their States - Simple Talkįoreign keys enforce referential integrity and in this way contribute to the database’s consistency.

Redgate sql toolbelt 2018 keygen