Adding Constraints script causing foreign key constraing err
gdmoffitt
Posts: 14
I'm running SQL Data Compare (SDC) 6.0.0.1124 to compare two versions of a database, let's call version 1.1 and 1.2. I'm attempting to 'promote' the changes by developers from the 1.2 database to the 1.1 database. The comparison finds 38 tables or views with differences, 717 tables or views with no difference, 143 tables that could not be compared.
The comparison takes about 10 minutes, then finishes, I save the script. I run the Synchronization Wizard, which shows the actions Drop Constraints (32 items), Delete rows (7 items), Update rows (18 items), Insert rows (27 items), add constraints (32 items).
The issue I'm running into is that it runs fine until the Add constraints section of the script. It then throws an error:
The following error message was returned from the SQL Server:
[547] The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_usrtHSHelocData_tblLoan". The conflict occurred in database "cadence", table "dbo.tblLoan", column 'loanID'.
The following SQL command caused the error:
DECLARE @pv binary(16)
-- Add constraints to [dbo].[usrtOvernightDeliverAuth]
ALTER TABLE [dbo].[usrtOvernightDeliverAuth] ADD CONSTRAINT [FK_usrtOvernightDeliverAuth_tblUser] FOREIGN KEY ([UpdatedByID]) REFERENCES [dbo].[tblUser] ([userID])
-- Add constraints to [dbo].[usrtHSHelocData]
ALTER TABLE [dbo].[usrtHSHelocData] ADD CONSTRAINT [FK_usrtHSHelocData_tblLoan] FOREIGN KEY ([LoanID]) REFERENCES [dbo].[tblLoan] ([loanID])
<snip> many other lines removed...
I manually ran the major parts of the script, which worked fine up to the Add Constraints script. Same error. I tried pairing off a few of the remove and add constraints statements and running them separately and they ran without error. So I'm perplexed as to why the add constraints part of the process, which is just reversing what was done in the remove constraints process, is giving me these errors.
Thanks
Glen Moffitt
The comparison takes about 10 minutes, then finishes, I save the script. I run the Synchronization Wizard, which shows the actions Drop Constraints (32 items), Delete rows (7 items), Update rows (18 items), Insert rows (27 items), add constraints (32 items).
The issue I'm running into is that it runs fine until the Add constraints section of the script. It then throws an error:
The following error message was returned from the SQL Server:
[547] The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_usrtHSHelocData_tblLoan". The conflict occurred in database "cadence", table "dbo.tblLoan", column 'loanID'.
The following SQL command caused the error:
DECLARE @pv binary(16)
-- Add constraints to [dbo].[usrtOvernightDeliverAuth]
ALTER TABLE [dbo].[usrtOvernightDeliverAuth] ADD CONSTRAINT [FK_usrtOvernightDeliverAuth_tblUser] FOREIGN KEY ([UpdatedByID]) REFERENCES [dbo].[tblUser] ([userID])
-- Add constraints to [dbo].[usrtHSHelocData]
ALTER TABLE [dbo].[usrtHSHelocData] ADD CONSTRAINT [FK_usrtHSHelocData_tblLoan] FOREIGN KEY ([LoanID]) REFERENCES [dbo].[tblLoan] ([loanID])
<snip> many other lines removed...
I manually ran the major parts of the script, which worked fine up to the Add Constraints script. Same error. I tried pairing off a few of the remove and add constraints statements and running them separately and they ran without error. So I'm perplexed as to why the add constraints part of the process, which is just reversing what was done in the remove constraints process, is giving me these errors.
Thanks
Glen Moffitt
Comments
May be worth a try certainly.
Project Manager
Red Gate Software Ltd
I did the update, that did not help with this particular issue. I'll keep banging away at it.