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.
_________________
What we do in life echoes in eternity <><
Randy Volters

[randyv]

I was looking at table constraints for a column constraint; sorry
_________________
What we do in life echoes in eternity <><
Randy Volters

[randyv]

OK, I'm stumped, not in contraint column usage either.
_________________
What we do in life echoes in eternity <><
Randy Volters

[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] ( COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_jc-time-sh_emp-id_20080818125445337] DEFAULT (''),
_________________
What we do in life echoes in eternity <><
Randy Volters

[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.


_________________
What we do in life echoes in eternity <><
Randy Volters

[Brian Donahue]

Hi Randy, thanks for letting us know it's all sorted out.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[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.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[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:

[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.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[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.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521