![]() ![]() However, if the data insertions that were performed during the period the foreign key was disabled have left the database table in an inconsistent state, we do not see an error immediately. We get the expected error message, which would seem to indicate that the foreign key is working as expected. INSERT INTO tableReferencing VALUES ( 1001 ) If we now try to insert a new row into the table that refers to a non-existent row, as follows: This seems to be the most obvious statement, since it just replaces the NOCHECK in the disabling statement with CHECK. The statement I have seen used most often looks like this:ĪLTER TABLE tableReferencing CHECK CONSTRAINT FK_References The data in our tables is now inconsistent, and looks like thisĪnd this is the point where many DBAs get confused about how to re-enable the foreign key. 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 enforce referential integrity and in this way contribute to the database’s consistency. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |