Automatically name primary key constraints

There are some things you see when writing t-sql code and schemas that just look sloppy, one of those is unnamed constraints, what you see is:

create table t( id int not null primary key )

The problem with this is that the primary key is given an automatic name which will look like “PK__t__3213E83F5F141958” - this means comparing database schemas for differences becomes hard as the constraints have different names.

The answer is to name the primary key, it is a really simple thing to do. What we want our table definition to look like is:

create table t ( id int not null, constraint [PK_t] primary key (id) )

If it is so simple, why not build a tool to do it for us!

I have released a tool that will do just that, if you grab the SSDT-Dev Pack at least version 1.1 from https://github.com/GoEddie/SSDT-DevPack/tree/master/release this adds a new menu to the tools menu in visual studio to name constraints. What I like to do is to go to “tools->options–>keyboard” and map an unused short-cut to the command “Tools.NameConstraints”, I used “ctrl+k + ctrl+n” so I can open a table in SSDT and just do ctrl+k and then ctrl+n and it automatically re-writes any tables in the active document that have unnamed primary keys with an appropriate name.

Things to know

Comments

I use the ScriptDom to parse the script, generate a syntax tree, modify it and re-write it (if it has changed) but the problem is that the ScriptDom doesn’t have a way to represent comments in the syntax tree so if you do this you lose any comments. I really agonised (and I mean that) over what to do about comments, in the end on the basis that deploying a table to sql would strip the comments and I can’t think of many cases where a table should have comments as part of the definition what I decided to do was take any comments and write them after that changed table so they are not lost and it should be easy to put them back where you want them.

config

You can configure the settings on a per-user basis by adding a config.xml file to %UsersProfile%\SSDTDevPack that looks like:

`

The PrimaryKeyName lets you create your own template of what the key should be called, you can use the tokens %TABLENAME% and %COLUMNNAME%.

You do not need to include all the generator options, if you miss them off then the default options will be used.

What’s next?

I am going to add a few more of these naming tools (probably to the same command) so you can name indexes etc in a consistent manor. If anyone wants to write one please feel free to add it into this project or create your own and share it :)