After 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.<\/p>\n
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. The two tables are tableReferenced<\/strong> and tableReferencing<\/strong>. As the name suggests the tableReferencing <\/strong>will reference the tableReferenced<\/strong> table.<\/p>\n To create and populate the two tables run:<\/p>\n INSERT INTO tableReferenced DEFAULT VALUES CREATE TABLE tableReferencing INSERT INTO tableReferencing VALUES ( 1 ) GO This will create the two tables and populates them like this:<\/p>\n The foreign key is established, and one can check this by inserting a row that would violate referential integrity:<\/p>\n This insert should fail, because there is no corresponding row in tableReferenced<\/span><\/strong>. SQL Server, as expected, reacts with the following error message:<\/p>\n The INSERT statement conflicted with the FOREIGN KEY constraint The statement has been terminated. <\/p>\n <\/code><\/p>\n To disable a foreign key in a SQL Server database, you can use a simple ALTER<\/strong> statement such as:<\/p>\n Following this statement on can insert the following row that would violate the referential integrity:<\/p>\n SQL Server confirms this, and the row has been inserted. No error messages were sent.<\/p>\n The data in our tables is now inconsistent, and looks like this<\/p>\n And this is the point where many DBAs get confused about how to re-enable the foreign key. The statement I have seen used most often looks like this:<\/p>\n This seems to be the most obvious statement, since it just replaces the NOCHECK<\/span><\/strong> in the disabling statement with CHECK<\/span><\/strong>.<\/p>\n If we now try to insert a new row into the table that refers to a non-existent row, as follows:<\/p>\n We get the expected error message, which would seem to indicate that the foreign key is working as expected.<\/p>\n 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. In the above example, we have a row in the tableReferencing<\/span><\/strong> table that is referencing a row with id 1000 in the tableReferences<\/span><\/strong> table. This row does not exist. If we execute a select<\/span><\/strong> statement that joins the two tables, we still get results, e.g. the following query executes without any errors:<\/p>\n <\/code><\/p>\n It will not return anything for the violating row. However, other queries may be less lucky, and applications may not be ready to handle their results.<\/p>\n One can easily check if the table is consistent, and whether referential integrity is satisfied, by executing:<\/p>\n In our case it returns:<\/p>\n Indicating that there is a referential integrity violation.<\/p>\n At the moment we may think that the foreign key is back to the original state, i.e. the state that we had before we disabled it. However, this is not the case. The foreign key that we have now is in a so called “untrusted” state. You can see this by looking at the sys.foreign_keys<\/span><\/strong> system view on SQL Server 2005, or at the status<\/span><\/strong> column of the sysobjects<\/span><\/strong> table on SQL Server 2000.<\/p>\n If we wanted to create a new foreign key that was in exactly the same state as the current one, we would need to execute:<\/p>\n Note the WITH NOCHECK<\/span><\/strong> part.<\/p>\n When we re-enabled the disabled foreign key, SQL Server did not perform checks to ensure that the tables involved in the foreign key relationship satisfied the constraints imposed by that foreign key. This made that operation fast, since SQL Server did not need to scan the relevant tables, but this was at the expense of database integrity.<\/p>\n To change the foreign key back to its original state, we need to let SQL Server check the validity of the constraint. To do this, the statement we should have used is:<\/p>\n This statement will make SQL Server check the data, and in our case it will find that the data in the tables is inconsistent with the foreign key constraint. On fixing these issues (in this case just dropping the violating row from the table tableReferencing<\/strong><\/span>), the state of the foreign key is restored to its original state. This will flip back the is_trusted<\/strong> flag in sys.foreign_keys<\/strong>, as well as enabling the constraint.<\/p>\n So to see which foreign keys are enabled or disabled, you can check the sys.foreign_keys<\/strong> table on 2005. It has a is_disabled<\/strong> column that has the value of 1 if the foreign key is disabled. Similarly you can get the information about whether or not a constraint is trusted.<\/p>\n When you disable a foreign key, not only will SQL Server disable it, it will, in addition, flip its state to non-trusted. When you enable it with the CHECK<\/strong> keyword, the trustedness of the foreign key is not restored. You can alter a foreign key’s state using an alter statement on the table. The CHECK<\/strong> and NOCHECK<\/strong> parts control whether the foreign key is enabled or disabled. The WITH CHECK<\/strong> and WITH NOCHECK<\/strong> options control whether SQL Server should check the consistency of the tables involved, and this way establish whether the foreign key is trusted or not. You can also use DBCC CHECKCONSTRAINTS <\/strong>to check whether the referential integrity constraint is satisfied on a particular table. <\/p>\n","protected":false},"excerpt":{"rendered":" Andr\u00e1s Belokosztolszki, Red Gate developer, shows how foreign key constraints can be disabled and re-enabled in order to simplify operations such as the bulk loading of data into related tables…<\/p>\n","protected":false},"author":9298,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4170,4794,4150],"coauthors":[],"class_list":["post-276","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-database-administration","tag-foreign-key-constraint-bulk-loading-referential-integrity","tag-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/276","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/9298"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=276"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/276\/revisions"}],"predecessor-version":[{"id":92286,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/276\/revisions\/92286"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=276"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=276"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=276"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=276"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}CREATE TABLE tableReferenced
( colA INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
, colB NVARCHAR(20) DEFAULT N'some data'
)
GO <\/p>\n
INSERT INTO tableReferenced DEFAULT VALUES
GO <\/p>\n
(
colC INT NOT NULL
PRIMARY KEY
IDENTITY(2, 2),
colARef INT NOT NULL
CONSTRAINT FK_References
REFERENCES tableReferenced ( colA )
)
GO <\/p>\n
INSERT INTO tableReferencing VALUES ( 1 )
INSERT INTO tableReferencing VALUES ( 2 ) <\/p>\n
<\/code><\/p>\n![\"404-andras1.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/404-andras1.jpg\")
<\/p>\nINSERT INTO tableReferencing VALUES ( 1000 )<\/code><\/p>\nMsg 547, Level 16, State 0, Line 1 <\/p>\n
\"FK_References\". The conflict occurred in database \"FKplay\",
table \"dbo.tableReferenced\", column 'colA'. <\/p>\nALTER TABLE tableReferencing NOCHECK CONSTRAINT FK_References<\/code><\/p>\nINSERT INTO tableReferencing VALUES ( 1000 )<\/code><\/p>\n![\"404-andras2.jpg\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/404-andras2.jpg\")
<\/p>\nALTER TABLE tableReferencing CHECK CONSTRAINT FK_References<\/code><\/p>\nINSERT INTO tableReferencing VALUES ( 1001 )<\/code><\/p>\nSELECT *
FROM tableReferencing
INNER JOIN tableReferenced ON tableReferencing.colARef = tableReferenced.colA <\/p>\nDBCC CHECKCONSTRAINTS ('tableReferencing') <\/code><\/p>\nTable Constraint Where
------------------------- ---------------- -------------------
[dbo].[tableReferencing] [FK_References] [colARef] = '1000'
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
<\/code><\/p>\nALTER TABLE tableReferencing WITH NOCHECK
ADD CONSTRAINT FK_References FOREIGN KEY ( colARef )
REFERENCES dbo.tableReferenced ( colA ) <\/code><\/p>\nALTER TABLE tableReferencing WITH CHECK CHECK CONSTRAINT FK_References <\/code><\/p>\nSummary<\/h2>\n