getting an error about a constraint that doesn't exist?

randyv

I've been using SQL Compare to update tables on an ODS (copy of production database).

Got this error on a table update:
[2714] There is already an object named 'DF_jc-time-sh_emp-id_20080818125445337' in the database.
Could not create constraint. See previous errors.

I'm not quite clear on why I'm getting the error because a check of
INFORMATION_SCHEMA.TABLE_CONSTRAINTS on the target database reveals that this doesn't appear to exist on the target database.

I'm not using SQL Source Control, so the advice of creating a migration script isn't helpful to me.

Any guidance would be appreciated.

randyv

I was looking at table constraints for a column constraint; sorry

randyv

OK, I'm stumped, not in contraint column usage either.

randyv

Here is the offending part of the statement...
I dropped the table, then when I try to create it again, I get the same error as reported at the top of the thread.

It is coming from the constraint on the [emp-id] column, but if the table was dropped, how could the object DF_jc-time-sh_emp-id_20080818125445337 still exist? More importantly, how can I drop it?

CREATE TABLE [dbo].[jc-time-sh]
(
[emp-id] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_jc-time-sh_emp-id_20080818125445337] DEFAULT (''),

randyv

Ok, I figured it out. NO ISSUE WITH SQL COMPARE; issue is with the dummy that created a table that was identical to the table that SQL COMPARE had problems with... namely me.

:oops:

Brian Donahue

Hi Randy, thanks for letting us know it's all sorted out.

nsams

I am running a database conversion tool of ours that uses SQL Compare and I keep getting the error:

Adding constraints to [dbo].[tblMAMSRBHeader]
There is already an object named 'DF__tblMAMSRB__Concu__60FC61CA' in the database.
Could not create constraint. See previous errors.

I look at tblMAMSRBHeader and it does not have a constraint with that name. However, I did find it under a different table with a similar name tblMAMSRBContributions.

Is there a way to tell SQL Compare to just give it a different name if it finds one of the same name already there?

Brian Donahue

I think this is the sort of thing that you would have to sort out manually. SQL Compare has an "ignore names on constraints" option but I believe that works on the comparison and not on the synchronization.

nsams

This is now happening on another database of ours with a different table and constraint. Are you sure that there is no solution for this? I can't believe that our only solution would be to wait for someone to get this error, then have to go in manually and delete the constraint and then ask them to re-convert their database.

It is very hard to fix this programmically. SQL Server does not let you directly delete records out of the default constraints table (sys.default_constraints). You have to do a ALTER statement on the table. The problem with that is that we don't know what table it is under until the error happens. I have been able to write a query that looks up the table name:

Select sys.objects.name from 
sys.default_constraints
inner join sys.objects ON sys.default_constraints.parent_object_id = sys.objects.object_id
where sys.default_constraints.name = 'DF__tblPOBEEm__Benef__5D0B3BC8'

And then I have to see how hard it would be to put that into a drop statement and have that run before the structural comparison. Even if this all works...someone else can call in the next day with a new constraint that's giving them issues and I would have to write a new query with drop for the new constraint name.

There must be a better way!

Brian Donahue

This has been brought up with our development team (sc-6104) - it should only happen in the rare circumstance where you rename tables and trip over system-generated constraint names (that contain parts of the new table names). At some point in the future, SQL Compare will finally support "re-runnable" scripts that do these checks, but for now, unfortunately you have to try to fix bits of the information schema manually.

retm1109

I have a development database. And a test database.

I added two columns to a table, updated (2) views, (3) procedures to support new columns and I received this on a update from Development to Test.

The following error message was returned from the SQL Server:

There is no table: Docs].[tmp_rg_xx_ContentNodes. I'm guessing this is some work table being used by SQL Compare application.

[2714] There is already an object named 'DF__ContentNo__Activ__53D770D6' in the database.
Could not create constraint. See previous errors.

The following SQL command caused the error:

CREATE TABLE [Docs].[tmp_rg_xx_ContentNodes]
(
[...
[Active] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__ContentNo__Activ__53D770D6] DEFAULT ('A')
)


The following messages were returned from the SQL Server:

[5701] Changed database context to 'WP3Test'.
[0] Dropping extended properties

retm1109

I have found your product does not like constraints very well. I had several constraints fail in a comparison of a development and a test database whereby we introduced (2) column changes ( additions ) and (3) view changes, and 4 procedure changes to support new columns. The offending columns were not related to the changes either. Just happened to be columns with constraints.

Brian Donahue

The only circumstance where I was able to reproduce this behavior was when I generated a sync script by comparing two databases, and running it on a third "similar" database. This happened because SQL Server names the default constraint in a predictable way.

If you are not running the generated script against a database that was not part of the original comparison, please let me know, so we can proceed with your support ticket (F0070615) and we can get some more information from you, because it would be unlikely that this problem has the same cause as the one mentioned in this forum topic, and you're having a new, as yet unknown, problem.

jspedick

Hi, I am running into a similar issue. We are adding a new default constraint to a table. The addition of the new column with the constraint is detected and included in the deployment as part of the table creation, however there is no if exists statement to check for its existence of the constraint and drop it if it exists. This means our generated deployment script is not re-runnable. Is this a known issue? It does check for existence of the primary key and other index on the table and drops them if they exist, but no check on the newly created default constraint.