Comparison does not recognize existing constraints in target database

mbix

When comparing two databases we've noticed that constraints setting default values are not being recognized on the target server. For example:
"[PublicHousing] [bit] NOT NULL CONSTRAINT [DF_Interview_PublicHousing] DEFAULT ((0))" ==> "[PublicHousing] [bit] NOT NULL, "
SQL Compare will generate an ALTER statement to add it, which fails as the named constraint already exists.

There are several tables in the comparison that are legitimately missing these constraints but there is no distinction between the constraints that exist and the ones that are missing.

Has anyone seen this behavior and is the a setting we are overlooking for the constraints to be recognized?

AshaPatel

Hello,

Sorry to hear you're hitting a problem!

I'm having some trouble reproducing the issue you mentioned. Did you mean your source column is

[PublicHousing] [bit] NOT NULL CONSTRAINT [DF_Interview_PublicHousing] DEFAULT ((0))

and your target column is

[PublicHousing] [bit] NOT NULL

?

I have tried a deployment like this and I can see Compare correctly creating the constraint:

PRINT N'Adding constraints to [dbo].[table]'
GO
ALTER TABLE [dbo].[table] ADD CONSTRAINT [DF_Interview_PublicHousing] DEFAULT ((0)) FOR [PublicHousing]
GO

I don't understand how this is failing due to the named constraint already existing, as the named constraint doesn't exist in the target column. Am I misunderstanding something?

Thanks,
Asha

mbix

Hi Asha,
Thanks for commenting.
Sorry if I wasn't clear: the constraint in the target database is not being recognized.
The target column is displaying in the comparison as:

[PublicHousing] [bit] NOT NULL

but in reality is:

[PublicHousing] [bit] NOT NULL CONSTRAINT [DF_Interview_PublicHousing] DEFAULT ((0))

which is exactly what is in the source column.
Since the comparison doesn't see the constraint it generates ALTER statements for these which fail as the constraint already exists.
This is happening throughout our comparison on multiple tables. It is only on constraints where a default value is being generated.

Thoughts?

AshaPatel

Ah I see what you mean now. I've tried to replicate but I am seeing the constraint with the default value correctly picked up in the target column. What version of SQL Compare are you running? If you upgrade to the latest version (under Help --> Check for updates) do you still see the same problem?

If you're still hitting the problem could you please email support@red-gate.com; someone will be in touch to investigate further and try to resolve this.

Thanks,
Asha