Red Gate forums :: View topic - getting an error about a constraint that doesn't exist?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

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

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
randyv



Joined: 28 Feb 2007
Posts: 113

PostPosted: Thu Sep 20, 2012 9:36 pm    Post subject: getting an error about a constraint that doesn't exist? Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
randyv



Joined: 28 Feb 2007
Posts: 113

PostPosted: Thu Sep 20, 2012 9:48 pm    Post subject: duh Reply with quote

I was looking at table constraints for a column constraint; sorry
_________________
What we do in life echoes in eternity <><
Randy Volters
Back to top
View user's profile Send private message Send e-mail
randyv



Joined: 28 Feb 2007
Posts: 113

PostPosted: Thu Sep 20, 2012 9:50 pm    Post subject: double duh Reply with quote

OK, I'm stumped, not in contraint column usage either.
_________________
What we do in life echoes in eternity <><
Randy Volters
Back to top
View user's profile Send private message Send e-mail
randyv



Joined: 28 Feb 2007
Posts: 113

PostPosted: Thu Sep 20, 2012 9:54 pm    Post subject: more Reply with quote

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] (Cool 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
Back to top
View user's profile Send private message Send e-mail
randyv



Joined: 28 Feb 2007
Posts: 113

PostPosted: Fri Sep 21, 2012 1:32 pm    Post subject: FOUND IT Reply with quote

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.

Embarassed
_________________
What we do in life echoes in eternity <><
Randy Volters
Back to top
View user's profile Send private message Send e-mail
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Mon Sep 24, 2012 3:03 pm    Post subject: Reply with quote

Hi Randy, thanks for letting us know it's all sorted out.
Back to top
View user's profile Send private message
nsams



Joined: 01 Dec 2005
Posts: 7
Location: Tempe, AZ

PostPosted: Fri Nov 09, 2012 12:15 am    Post subject: I'm having same issue Reply with quote

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?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Fri Nov 09, 2012 10:08 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
nsams



Joined: 01 Dec 2005
Posts: 7
Location: Tempe, AZ

PostPosted: Mon Nov 26, 2012 10:28 pm    Post subject: Happening again Reply with quote

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 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!
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Tue Nov 27, 2012 10:55 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
retm1109



Joined: 12 Mar 2013
Posts: 2

PostPosted: Tue Mar 12, 2013 5:42 pm    Post subject: Same issue here Reply with quote

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
Back to top
View user's profile Send private message
retm1109



Joined: 12 Mar 2013
Posts: 2

PostPosted: Tue Mar 12, 2013 7:26 pm    Post subject: SQL Compare - Constraints Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Wed Mar 13, 2013 4:21 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group