Sql-server – Order to disable constraint in a SQL Server Database

check-constraintsconstraintsql serversql-server-2012

I have a DB that every day delete all the data and load new one at the night. To do this an SP disable all constraint with sp_msforeachtable execution. I created some new objects (Schemas, SP, Tables, etc.) and after this, starts to crash sending this message "The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "[Constraint_Name]". The conflict occurred in database "[DatabaseExample]", table "[Schema].[Table]", column '[Column_X]'"

I decided to disable it manually, but i don't know which is the right order to disabled without using hard coding.

First, do i need to disable Unique Keys or Foreign Keys?, if a FK referenced to another table i should start disabling the reference table?

Best Answer

You need to disable the constraint in the table that references the table mentioned in the error message (I'm sure that's not [Schema].[Table]). You can find this using the constraint name in the error message (which I'm also sure is not Constraint_Name, but I'll use that):

DECLARE @sql nvarchar(max);

SELECT @sql = N'ALTER TABLE ' 
  + QUOTENAME(OBJECT_SCHEMA_NAME([parent_object_id]))
  + N'.' + QUOTENAME(OBJECT_NAME([parent_object_id]))
  + N' NOCHECK CONSTRAINT ' + QUOTENAME(name) + N';'
FROM sys.foreign_keys WHERE name = N'Constraint_Name';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Check the print output, if it looks good, uncomment the exec, and run it again.

If you need to do this for multiple tables:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'
  ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME([parent_object_id]))
  + N'.' + QUOTENAME(OBJECT_NAME([parent_object_id]))
  + N' NOCHECK CONSTRAINT ' + QUOTENAME(name) + N';'
FROM sys.foreign_keys WHERE is_disabled = 0;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

The PRINT output will only be useful up to a certain point, depending on your output settings in SSMS (see a workaround here). If you need to narrow it down and not disable all foreign keys, then you can copy the rows you want to the top pane, disable them, then move on to the next set.

Now, if you do ultimately need these foreign key constraints, that means that the values you insert into the table that had the constraint already exist in the table mentioned in the error message, otherwise what is the point of the constraint? You need to fix your code so that it works correctly. Disabling or dropping the constraint is just masking a more important problem, and is going to allow bad data to get into the table in the meantime.

Disabling the constraint is like doing up your seatbelt behind your back just to shut the car up; dropping it is like ripping out the seatbelt and the speaker.

Related Question