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

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

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

Postby randyv » Thu Sep 20, 2012 9:36 pm

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
 
Posts: 113
Joined: Wed Feb 28, 2007 3:27 pm

duh

Postby randyv » Thu Sep 20, 2012 9:48 pm

I was looking at table constraints for a column constraint; sorry
What we do in life echoes in eternity <><
Randy Volters
randyv
 
Posts: 113
Joined: Wed Feb 28, 2007 3:27 pm

double duh

Postby randyv » Thu Sep 20, 2012 9:50 pm

OK, I'm stumped, not in contraint column usage either.
What we do in life echoes in eternity <><
Randy Volters
randyv
 
Posts: 113
Joined: Wed Feb 28, 2007 3:27 pm

more

Postby randyv » Thu Sep 20, 2012 9:54 pm

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 (''),
What we do in life echoes in eternity <><
Randy Volters
randyv
 
Posts: 113
Joined: Wed Feb 28, 2007 3:27 pm

FOUND IT

Postby randyv » Fri Sep 21, 2012 1:32 pm

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:
What we do in life echoes in eternity <><
Randy Volters
randyv
 
Posts: 113
Joined: Wed Feb 28, 2007 3:27 pm

Postby Brian Donahue » Mon Sep 24, 2012 3:03 pm

Hi Randy, thanks for letting us know it's all sorted out.
Brian Donahue
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

I'm having same issue

Postby nsams » Fri Nov 09, 2012 12:15 am

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?
nsams
 
Posts: 7
Joined: Thu Dec 01, 2005 7:41 pm
Location: Tempe, AZ

Postby Brian Donahue » Fri Nov 09, 2012 10:08 am

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
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Happening again

Postby nsams » Mon Nov 26, 2012 10:28 pm

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:

Code: Select all
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!
nsams
 
Posts: 7
Joined: Thu Dec 01, 2005 7:41 pm
Location: Tempe, AZ

Postby Brian Donahue » Tue Nov 27, 2012 10:55 am

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
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am

Same issue here

Postby retm1109 » Tue Mar 12, 2013 5:42 pm

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
Last edited by retm1109 on Tue Mar 12, 2013 7:57 pm, edited 1 time in total.
retm1109
 
Posts: 2
Joined: Tue Mar 12, 2013 5:40 pm

SQL Compare - Constraints

Postby retm1109 » Tue Mar 12, 2013 7:26 pm

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.
retm1109
 
Posts: 2
Joined: Tue Mar 12, 2013 5:40 pm

Postby Brian Donahue » Wed Mar 13, 2013 4:21 pm

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
 
Posts: 6669
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests