Apex SQL Diff: Managing existing Data Updates with Table Constraints

ApexSQL Diff , SQL Add comments

When using SQL Diff, should the tables you need to script be related via referential integrity, ApexSQL Diff has the ability to figure out the relationships in the tables, in most cases, scripting them so that referential integrity problems don't exist in the genreated script.

Version 2008.11 has a more accurate and optimised parser that may help but if you ever find that you cannot get a script to run when updating data in a table with constraints you can check the following. 

If when you run the script - you get an error like the one below

 Running 'Script' script... 
Backing up database databaseName Processed 856 pages for database databaseName', file databaseName' on file 1.
Processed 1 pages for database 'databaseName', file 'databaseName' on file 1. 
BACKUP DATABASE successfully processed 857 pages in 0.048 seconds (146.112 MB/sec). 
Changed database context to 'databaseName'. 
Error 547, Severity=16, Line=49 DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_tblConflictingTableName_tblAnotherTable'.
The conflict occurred in database 'databaseName', table 'tblConflictingTableName', column 'conflictingTableNameFieldID'. 
Script Operation With Errors.
If you examine the error message you'll see that the important line shows a conflict with a COLUMN REFERENCE in the database: DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_tblConflictingTableName_tblAnotherTable'. The conflict occurred in database 'databaseName', table 'tblConflictingTableName', column 'conflictingTableNameFieldID'.

Therefore in order to rectify \ bypasss this you need to do the following:

Extract the key items from the error message and populate the template below:

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_tblCategoryIndexValues_tblYears'. The conflict occurred in database 'databaseName', table 'tblConflictingTableName', column 'conflictingTableNameFieldID'. ALTER TABLE [TABLE_NAME] NOCHECK CONSTRAINT [CONSTRAINT_NAME] ALTER TABLE [TABLE_NAME] CHECK CONSTRAINT [CONSTRAINT_NAME] Add these statements to the script (see the shortened example below). NOCHECK statements go after the BEGIN TRAN and the CHECK statements go after the queries have been executed. ...

 BEGIN TRAN -- ======================================================= 
-- Synchronization Script for Table: [dbo].[tblTableName] 
-- ======================================================= 
Print 'Synchronization Script for Table: [dbo].[tblTableName]' 
ALTER TABLE tblTableNameRlnshps NOCHECK CONSTRAINT FK_tblTableNameRlnshps_tblTableName 
ALTER TABLE tblAnotherTableNameRlnshps NOCHECK CONSTRAINT FK_tblAnotherTableNameRlnshps_tblTableName 
ALTER TABLE tblMainData NOCHECK CONSTRAINT FK_tblMainData_tblTableName 
DELETE FROM [dbo].[tblTableName] WHERE [tableNameID]=6 

-- =======================================================
-- Synchronization Script for Table: [dbo].[tblTableName] 
-- ======================================================= 
Print 'Synchronization Script for Table: [dbo].[tblTableName]' 
SET IDENTITY_INSERT [dbo].[tblTableName] ON 
DELETE FROM [dbo].[tblTableName] WHERE [tableNameID] = 1 
INSERT INTO [dbo].[tblTableName] ([tableNameID], ...) VALUES (1, ...) 
SET IDENTITY_INSERT [dbo].[tblTableName] OFF 
ALTER TABLE tblTableNameRlnshps CHECK CONSTRAINT FK_tblTableNameRlnshps_tblTableName 
ALTER TABLE tblAnotherTableNameRlnshps CHECK CONSTRAINT FK_tblAnotherTableNameRlnshps_tblTableName 
ALTER TABLE tblMainData CHECK CONSTRAINT FK_tblMainData_tblTableName
COMMIT ... 

It may take a few executions to catch and script out all the constraints but it's quicker that re-writing the UPDATE statements!!

  • ENSURE YOU ALWAYS FOLLOW YOUR NOCHECK STATEMENT WITH A CHECK STATEMENT TO RE-ENABLE THE CONSTRAINT!
  • ENSURE THE SCRIPT ISN'T ACTUALLY BREAKING THE INTEGRITY OF THE DATA E.G. DELETING A RECORD WITH A PRIMARY KEY THAT EXISTS AS A FOREIGN KEY IN ANOTHER TABLE.

 

Bookmark and Share

0 responses to “Apex SQL Diff: Managing existing Data Updates with Table Constraints”

Leave a Reply

Leave this field empty:

Powered by Mango Blog. Design and Icons by N.Design Studio