Doing Something About Auto Generated Names

Posted by & filed under , , , , , .

Zombie Apocafest 2009 - Shaun & EdI like my name.  It provides a point of reference to who I am.  If I am at work and someone calls for “Jason Strate”… that’s me.  If I am at home, the same thing can happen and I still know that it’s me.  I know that no matter where I am, my name will be a constant and it means something when it is called.  Hopefully, it doesn’t mean “late to dinner”.

This same principle applies to the tables and the objects related to those tables.  We give that table a name in the development environment, maybe it’s called “ZombieBaconUnicorn”.  We expect that it will be called “ZombieBaconUnicorn”.  Unfortunately, this doesn’t always ring true with other database objects related to tables; such as Primary Keys and Defaults Constraints.

Random Naming

There are multiple ways to create Primary Key and Default Constraints and they don’t all have an implied name.  Take for instance the following script, what would you assume the names of the Primary Key and Default Constraints would be?

CREATE TABLE dbo.RandomPKandDC
(
RandomPKandDCID int IDENTITY(1,1) PRIMARY KEY CLUSTERED
,Column1 datetime DEFAULT(GETDATE())
)

SELECT name FROM sys.objects
WHERE parent_object_id = OBJECT_ID('dbo.RandomPKandDC')

Did you guess the following names?

image

No?!  Run it on you machine, you’ll get your own unique names.

Non-Random Naming

Getting around the random naming is pretty simple.  Below I’ll would you through the steps.

The first change is how you’ll define the PRIMARY KEY on the table with the CREATE TABLE statement.  Instead of adding PRIMARY KEY CLUSTERED on the column definition, add it to the table as a constraint.

CREATE TABLE dbo.NonRandomPKandDC
(
NonRandomPKandDCID int IDENTITY(1,1)
,Column1 datetime
,CONSTRAINT PK_NonRandomPKandDC_RandomPKandDCID PRIMARY KEY CLUSTERED (NonRandomPKandDCID)
) 

Next you want to use the ALTER TABLE statement to add the DEFAULT CONSTRAINT.  Instead of making the default part of the column definition in the CREATE TABLE, you want to name the default as a constraint to name it as it is created.

ALTER TABLE dbo.NonRandomPKandDC ADD CONSTRAINT DF_NonRandomPKandDC_Column1
DEFAULT (getdate()) FOR [Column1] 

Take a look at the results of the next query:

SELECT name FROM sys.objects
WHERE parent_object_id = OBJECT_ID('dbo.NonRandomPKandDC')

image

On your server and mine the objects will have the same name.  When you deploy objects build from T-SQL like this from development to test to production they will have the same names.

What the!?

Now there isn’t anything wrong with these random names from the perspective of how your database will perform.  But it will hose up things such as database comparisons and investigating errors.

With database comparisons, the issue is pretty obvious.  If the contents of the object are the same but the name is different then it isn’t the exact same object.  Many tools have options for ignoring these names, but there are some that don’t.  Even when the tool has the option to ignore, you will need to rely on the operator of the tool to disable the feature.

Looking now at investigating errors, I am a big fan of errors that can tell me the problem within the database.  Which of the following is easier to understand.  The first with the random name:

Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint ‘PK__RandomPKandDC__5D60DB10′. Cannot insert duplicate key in object ‘dbo.RandomPKandDC’.
The statement has been terminated.

Or the second with the stated name:

Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint ‘PK_NonRandomPKandDC_RandomPKandDCID’. Cannot insert duplicate key in object ‘dbo.NonRandomPKandDC’.
The statement has been terminated.

Ok, maybe this isn’t the best example of this type of issue, but it does tell me exactly the issue and because I named the object explicitly I know where the problem is and something about the issue before digging into the schema of the table.

Overall, this is about reducing pain points in building and using your databases.  The easier it is to manage them once they are deployed, the more Bejeweled Blitz you can play on Facebook between your projects.

Renaming the Objects

Maybe I’ve sold you on this idea.  If so, you may be thinking “What the heck do you do with all of my existing databases objects?”  That answer is simple… rename them.  Since the names of these objects isn’t tied to how they are defined, you can easily get them renamed with sp_rename.

The following two scripts are what I use to rename PRIMARY KEY CONSTRAINTS and DEFAULT CONSTRAINTS.

This first one will provide sp_rename statements that you can execute to rename all of your PRIMARY KEY CONSTRAINTS:

;WITH PKNames
AS (
SELECT name AS IndexName
,OBJECT_NAME(object_id) AS TableName
,OBJECT_SCHEMA_NAME(object_id) as SchemaName
,(SELECT '' + c.name
FROM sys.index_columns ic
INNER JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.index_column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH('')) AS Columns
FROM sys.indexes i
WHERE i.is_primary_key = 1
)
SELECT 'EXEC sp_rename ''' + QUOTENAME(SchemaName) + '.' + QUOTENAME(IndexName) + ''', ''PK_' + TableName + '_' + Columns + ''''
FROM PKNames
WHERE IndexName <> 'PK_' + TableName + '_' + Columns

The second one will provide sp_rename statements that you can execute to rename all of your DEFAULT CONSTRAINTS:

SELECT 'EXEC sp_rename ''' + QUOTENAME(OBJECT_SCHEMA_NAME(dc.parent_object_id)) + '.'
+ QUOTENAME(dc.name) + ''', ''DF_' + OBJECT_NAME(dc.parent_object_id) + '_' + c.name + ''''
FROM sys.default_constraints dc
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
WHERE dc.name <> 'DF_' + OBJECT_NAME(dc.parent_object_id) + '_' + c.name

Hopefully these can be helpful for you as well.

Conclusion

In a roundabout way, this post has been about naming conventions.  Make the world a prettier place by having a convention on naming the objects in your database.

Last word… don’t just run out and use this in your production database without doing your own testing and promoting using your own company’s deployment process.  An obvious statement but one that I thought I should point out since I know how tempting this can be.

EDIT 2010/05/27: Modified scripts to account for schema variations.

  • http://www.simple-talk.com/community/blogs/jonathanallen Jonathan Allen

    I guess this is like the naming of a child when its born as opposed to the orphanage making up a name when they take on a child. Its really down to the 'creators' to decide on the appropriate name for everyone to use when they want to address the subject. Or maybe I have had more beer than I should have and am over the limit for reading SQL Blogs. :-/ Liking the rename scripts though, they could come in handy.

    Many thanks

    http://twitter.com/fatherjack

    • http://feeds2.feedburner.com/StrateSql jstrate

      I don't really care what they name them. Main bit was the scripts. Though I do appreciate something consistent to make compares easier.

  • Calvin Jones

    I get an error when trying to run the rename for DEFAULT CONSTRAINTS.

    Msg 15225, Level 11, State 1, Procedure sp_rename, Line 332

    No item by the name of 'DF_tblPoolDetail_CreatedDate' could be found in the current database 'Jury', given that @itemtype was input as '(null)'.

    • http://feeds2.feedburner.com/StrateSql jstrate

      What version of SQL Server are you using? I checked the code on the SQL Server 2005 and SQL Server 2008 instances that I had and hadn't had issues.

    • http://feeds2.feedburner.com/StrateSql jstrate

      Took a couple minutes, but I wasn't accounting for schema variations in the scripts I provided. I've updated to include those now.

  • Pingback: Tweets that mention Doing Something About Auto Generated Names | Strate SQL -- Topsy.com()

  • http://bradsruminations.blogspot.com/ Brad Schulz

    Hi Jason…

    You probably already know this, but I'll post it here for reference… The DEFAULT constraint name can be defined directly in the CREATE TABLE… no need to do a separate ALTER TABLE to establish names for any DEFAULTs:

    CREATE TABLE dbo.NonRandomPKandDC

    (

    ,Column1 datetime CONSTRAINT DF_NonRandomPKandDC_Column1 DEFAULT (GETDATE())

    )

    I like the idea of the script to rename the PRIMARY KEY and DEFAULT constraints.

    –Brad

    • http://feeds2.feedburner.com/StrateSql jstrate

      Yeah, I had thought about including that method as well. For some reason I always split them apart. No real reason besides habit.

  • http://angelescarinsurancelos.net Benjamin Kim

    I guess this is like the naming of a child when its born as opposed to the orphanage making up a name when they take on a child. Its really down to the ‘creators’ to decide on the appropriate name for everyone to use when they want to address the subject. Or maybe I have had more beer than I should have and am over the limit for reading SQL Blogs. :-/ Liking the rename scripts though, they could come in handy.
    +1

  • Pingback: Naming and Renaming Database Constraints | Dave's SQL Blog()