I ran into an interesting problem recently, and learned something new as a result. This problem involved naming and renaming of database constraints, specifically primary key, foreign key and default constraints.
The SQL Server database I was working with had been around for a while, and like a lot of legacy databases out there, this one wasn’t designed using any kind of consistent standards. A lot of work had been done recently to try and clean things up: obsolete objects had been removed; tables, views, sprocs and functions had been refactored and renamed, etc. The next step was looking at naming the constraints consistently.
Like most things in SQL, there are a few different ways to create constraints. Take primary keys for example – a PK can be created either when the table is created or it can be added to an existing table, either with or without an explicit name. For example:
CREATE TABLE dbo.SampleTable1 ( F1 int NOT NULL, CONSTRAINT PK_SampleTable1 PRIMARY KEY CLUSTERED (F1) ); CREATE TABLE dbo.SampleTable2 ( F2 int NOT NULL, PRIMARY KEY CLUSTERED (F2) );
Notice that on the first table, I assigned a name for the primary key (PK_SampleTable1), but on the second table, I did not. The PK on the second table still gets a name though, but it turns out to be really ugly one.
The name that gets assigned by SQL (PK__SampleTa__32149277145C0A3F) is a little puzzling. It uses a PK prefix, then part of the table name, then a numeric sequence, all separated by double underscores. Seems like they could have made a different choice on that one. Note though, that if you create the constraint using SSMS table designer, it puts a nice constraint name in there for you (like PK_TableName).
Of course the ugliness factor is annoying, but a bigger problem is that if you drop and recreate this table, the PK will get a new name every time. Same problem if you create the table in a different environment, or if you use a tool to do a database comparison. Really ugly and annoying.
So, here’s the part where I learned something new. Normally, when I use SSMS to script out a table that has a primary key, I expect to see the PK defined in as part of the table creation with its name like so…
CREATE TABLE [dbo].[SampleTable1]( [F1] [int] NOT NULL, CONSTRAINT [PK_SampleTable1] PRIMARY KEY CLUSTERED ( [F1] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
However, if you use SSMS to script the second table, it looks something like this:
CREATE TABLE [dbo].[SampleTable2]( [F2] [int] NOT NULL, PRIMARY KEY CLUSTERED ( [F2] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
Clearly SQL is treating the constraint with the ugly name differently than it’s treating the one with the name I assigned. Seems odd, and got me thinking, how does SQL know that “PK__SampleTa__32149277145C0A3F” was created by SQL and not by me? After all, I could have chosen that name myself, right?
It turns out there’s a field in the system table sys.key_constraints called is_system_named. It gets set to 0 when the constraint name is user assigned, and 1 when the name is system generated. When set to 1, it tells SQL that the constraint name doesn’t hold any significance to the user, so feel free to ignore it when scripting.
Similarly, there is a field with the same name in sys.foreign_keys and sys.default_constraints, serving the same purpose for foreign keys and default constraints.
You can rename poorly named constraints by either by dropping and recreating the constraint (which may not be feasible on large tables), or by renaming the constraint in place using the stored procedure sp_rename. For my project, we chose to use sp_rename like so…
exec sp_rename ‘PK__SampleTa__32149277145C0A3F’, 'PK_SampleTable2', 'OBJECT';
Running this sproc as shown renames the constraint and sets the is_system_named field to 0, so when you script the table now, you get your constraint with a name in the script as expected.
If you have a lot of constraints that need to be renamed (as was the case in my project), doing them one at a time is a lot of work. Rather than reinventing the wheel, I found a few examples online of scripts for automatically generating the exec statements to rename all of the constraints that don’t follow a particular naming convention. The best I found was a blog post from Jason Strate (blog | @stratesql) which gives more good information about this problem. I used a modified version of his script is near the end of this post.
Another way to rename constraints is through SSMS in the object explorer GUI (by right-clicking on the object and selecting “rename”). If you choose to do that for a constraint with a system generated name, the constraint is renamed as expected, but the is_system_named field in sys.key_constraints is not set to 0. This can be frustrating, because you think you did your job, but scripting the table gives an unnamed PK again.
When renaming through SSMS, sp_rename gets called behind the scenes to make the name change. Using profiler, I found that the sp_rename call is different however, it’s using ‘INDEX’ instead of ‘OBJECT’ as the third parameter.
Apparently, when ‘INDEX’ is passed, the is_system_named field is not reset. There is a connect item for this originally submitted by John Bell, and I added a comment to clarify it a bit.
- My advice is to use a consistent naming convention for your constraints, and don’t allow SQL to assign a constraint name for you.
- If you need to rename existing constraints, use sp_rename with the ‘OBJECT’ parameter and not SSMS.
- Here are the standard naming conventions I use. From my experience, these or very similar conventions are pretty widely used.
Primary Key – PK_TableName
Foreign Key – FK_ChildTableName_ParentTableName (optionally add field names if there are multiple FKs between a child and a parent)
Default – DF_TableName_FieldName
Note: add the schema name before the table name for each of these where the schema is not dbo.