locked
Stumped - constraint names of default value constraints RRS feed

  • Question

  • So, I'm more than a little stumped on this one.

    We have a datatabase that exists on a MSDE 2000 server, as part of the upgrade to a new version of the product, we do a schema migration.

    This involves chaining a bunch of SQL Statements together and executing them. This has always worked well, but we've come across a problem recently which is more than a little bizarre.

    On a particular table, we have a series of NOT NULL columns that have default values. Being the lazy developers we are, we haven't gone to the trouble of naming constraints to deal with these default values, instead letting SQL Server generate a name for us.

    So as part of the schema migration we'll run a command like

    ALTER TABLE dbo.CUSTOMER ADD
        C_ACCEPTED BIT NOT NULL DEFAULT 0
    GO

    in our latest release, this statement (table names changed to protect the guilty) is chained together with a whole bunch of others. It throws an error on this statement saying  There is already an object named 'DF__CUSTOMER__C___2A01329B' in the database.

    Now, this default constraint already exists - for a totally different column that already exists on the table.

    The chain of statements work successfully on a 2005 server hosting the same database,

    If I try to run the statement by itself on MSDE 200, it works correctly and the new column is added.

    It's only when the statements are chained together that we get this error.

    So, my questions are
    a) How are the constraint names assigned for default values like this?
    b) Has anyone seen this before?
    c) Short of adding going through the migration code and naming the default value constraints myself, is there anything I can do?



    Thursday, April 26, 2007 6:39 PM

Answers

  • Hokey, I'll answer it more from the view of ALWAYS naming all PK, FK and DEFAULT constraints... I never let SQL Server name them (on purpose).  Say you want to compare a test and prod database for example, tools like SQL Compare will see all those name differences and not give you a clean compare.  Always name your own.  A sample script to change the names is as follows...  If you use a mixture of BOUND defaults and CONSTRAINTS, then you need to modify this SQL or the output so you don't rename BOUND defaults...  There is maybe a better set of tables to use under SQL 2005, but this works too.  So, say you want your defualt constraint names to be in this format  DF_tablename_columnname... this script will build renames of the objects...  Sorry, can't assit with the issues on teh dups, cause I name constraints to a standard...

     

    Bruce

     

     

    select 'exec sp_rename ' + o.name + ', DF_' + t.name + '_' + c.name

    from syscolumns c

    inner join sysobjects o

    on c.cdefault = o.id

    inner join sysobjects t

    on c.id = t.id

    where c.cdefault <> 0

    and o.name <> 'DF_' + t.name + '_' + c.name

    order by o.name

     

     

    Thursday, April 26, 2007 8:12 PM
  • You should never get an error if SQL Server generates a name automatically for the constraint. We always generate unique names based on combination of table, column and uniqeufier value. Is it possible you are specifying the system generated constraint name in a statement? However, as you have observed it is best to name the constraints on all permanent objects to simplify code analysis and schema comparison operations.
    Thursday, April 26, 2007 11:56 PM

All replies

  • Hokey, I'll answer it more from the view of ALWAYS naming all PK, FK and DEFAULT constraints... I never let SQL Server name them (on purpose).  Say you want to compare a test and prod database for example, tools like SQL Compare will see all those name differences and not give you a clean compare.  Always name your own.  A sample script to change the names is as follows...  If you use a mixture of BOUND defaults and CONSTRAINTS, then you need to modify this SQL or the output so you don't rename BOUND defaults...  There is maybe a better set of tables to use under SQL 2005, but this works too.  So, say you want your defualt constraint names to be in this format  DF_tablename_columnname... this script will build renames of the objects...  Sorry, can't assit with the issues on teh dups, cause I name constraints to a standard...

     

    Bruce

     

     

    select 'exec sp_rename ' + o.name + ', DF_' + t.name + '_' + c.name

    from syscolumns c

    inner join sysobjects o

    on c.cdefault = o.id

    inner join sysobjects t

    on c.id = t.id

    where c.cdefault <> 0

    and o.name <> 'DF_' + t.name + '_' + c.name

    order by o.name

     

     

    Thursday, April 26, 2007 8:12 PM
  • You should never get an error if SQL Server generates a name automatically for the constraint. We always generate unique names based on combination of table, column and uniqeufier value. Is it possible you are specifying the system generated constraint name in a statement? However, as you have observed it is best to name the constraints on all permanent objects to simplify code analysis and schema comparison operations.
    Thursday, April 26, 2007 11:56 PM
  • Looking at the constraint name, I can see that its name is generated bsed on the table, the column and an identifier.

    But what is the identifier?

    I would have assumed that this would have been the id of the column, or that converted to hex.

    Looking at it a bit closer, I see that the name of the constraint is a combinantion of the table name, the column name and the cdefault id of that columns entry in the syscolmns table converted to hex. (which, is a bunch of information that really, I don't want to know Wink)

    The wierd thing is that there is no cdefault with a value that matches the name of the existing constraint - so it's all a bit bizarre.

    Regardless, I'm just going to go through and rename the objects and take from there.For posterity's sake, here's a corrected version of Bruce's script, with a cursor to execute the statements:

    DECLARE Statements CURSOR FOR
        select 'sp_rename ''' + o.name + ''', ''DF_' + t.name + '_' + c.name + ''', ''OBJECT'''
        from syscolumns c
        inner join sysobjects o
        on c.cdefault = o.id
        inner join sysobjects t
        on c.id = t.id
        where c.cdefault <> 0
        and o.name <> 'DF_' + t.name + '_' + c.name
        order by o.name

    OPEN Statements
    DECLARE @SqlStatement varchar(1000)

    FETCH NEXT FROM Statements INTO @SqlStatement

    WHILE @@FETCH_STATUS = 0
    BEGIN
        exec (@SqlStatement)

        FETCH NEXT FROM Statements INTO @SqlStatement
    END
        
    CLOSE Statements
    DEALLOCATE Statements
    GO

    Friday, April 27, 2007 9:08 AM
  • Hokey, thanks for the updated script. I like to generate the sp_rename script for the names I'd be changing and review them, just in case...  The script still will rename defaults that are BOUND to a column with an SP_BINDEFAULT command.  If you never do that, and you shouldn't, not an issue. 

     

    It looks like that number in the constraint is random, maybe part of a GUID relating to the time it was created?  I just kept dropping and recreating a defualt and it was always assigned a new number inside the constraint name, so it doesn't appear to be related to the column name/id, not JUST that anyway.

     

    But yea, always better to name all constraints yourself... PK, FK, DEFAULT.   You'll be very glad you did when you need to compare 2 databases that are SUPPOSED TO have the exact same structure...  You can't tell if a constraint difference is due to SQL-generated names or something else inside the constraint is different...

     

    Bruce

    Friday, April 27, 2007 1:33 PM
  • This is an old thread, but with this reply I hope to help others running into the same problem. This here is one possible explanation for the error "There is already an object named 'DF__TableName__Colum__2A01329B' in the database."

    The MS reply was, naturally, technically correct but utterly useless. Yes, generated constraint names are made unique with a suffix of 8 'random' hexadecimal digits. They just forgot to mention the fact that every individual database uses the exact same sequence of pseudo-random numbers. When you copy tables from one database to another with a script or tool that preserves (generated) constraint names, you have a (slim) chance of running into trouble later on, when trying to create additional tables/columns with similar names.

    A simple example:

    CREATE DATABASE Test1
    USE Test1
    CREATE TABLE dbo.TableName1 (ColumnName1 int DEFAULT 1)
    CREATE TABLE dbo.TableName2 (ColumnName2 int DEFAULT 1)
    EXEC sp_help TableName2		-- DF__TableName__Colum__00551192
    
    CREATE DATABASE Test2
    USE Test2
    -- first we 'copy' TableName2 from Test1 to Test2
    CREATE TABLE dbo.TableName2 (ColumnName2 int CONSTRAINT DF__TableName__Colum__00551192 DEFAULT 1)
    -- next, we create a new table - similar names for both table and column!
    CREATE TABLE dbo.TableName3 (ColumnName3 int DEFAULT 1)

    The last statement will throw this exception: "There is already an object named 'DF__TableName__Colum__00551192' in the database." This happens because the second constraint name generated in any (?) database has suffix 00551192.

    So what's the solution?

    1. Explicitly name your constraints (as already suggested by everyone else).
    2. If you really must use generated constraint names (typically some legacy), avoid copying these names from one database to another - rather let the destination server generate its own constraint names.
    3. If you run into the problem anyway, try changing the order in which you create the constraints, or create a few 'dummy' constraints first to fast-forward through the pseudo-random number sequence.

    With kind regards,

    Ruud

    • Proposed as answer by Naomi N Wednesday, July 6, 2011 3:17 PM
    Wednesday, July 6, 2011 11:26 AM