If you write code to SQL Server then you might be interested in this: I have written a tSQLt tdd training course which has helped over 500 people learn both tSQLt and how to apply TDD practices to their SQL Server T-SQL development, you can join the course at https://courses.agilesql.club.

Automatically name primary key constraints in SSDT

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


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.


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

<?xml version="1.0" encoding="utf-16"?> <Settings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <GeneratorOptions> <KeywordCasing>Lowercase</KeywordCasing> <SqlVersion>Sql90</SqlVersion> <IndentationSize>4</IndentationSize> <IncludeSemicolons>false</IncludeSemicolons> <AlignColumnDefinitionFields>true</AlignColumnDefinitionFields> <NewLineBeforeFromClause>true</NewLineBeforeFromClause> <NewLineBeforeWhereClause>true</NewLineBeforeWhereClause> <NewLineBeforeGroupByClause>true</NewLineBeforeGroupByClause> <NewLineBeforeOrderByClause>true</NewLineBeforeOrderByClause> <NewLineBeforeHavingClause>true</NewLineBeforeHavingClause> <NewLineBeforeJoinClause>true</NewLineBeforeJoinClause> <NewLineBeforeOffsetClause>true</NewLineBeforeOffsetClause> <NewLineBeforeOutputClause>true</NewLineBeforeOutputClause> <AlignClauseBodies>true</AlignClauseBodies> <MultilineSelectElementsList>true</MultilineSelectElementsList> <MultilineWherePredicatesList>true</MultilineWherePredicatesList> <IndentViewBody>false</IndentViewBody> <MultilineViewColumnsList>true</MultilineViewColumnsList> <AsKeywordOnOwnLine>true</AsKeywordOnOwnLine> <IndentSetClause>false</IndentSetClause> <AlignSetClauseItem>true</AlignSetClauseItem> <MultilineSetClauseItems>true</MultilineSetClauseItems> <MultilineInsertTargetsList>true</MultilineInsertTargetsList> <MultilineInsertSourcesList>true</MultilineInsertSourcesList> <NewLineBeforeOpenParenthesisInMultilineList>false</NewLineBeforeOpenParenthesisInMultilineList> <NewLineBeforeCloseParenthesisInMultilineList>true</NewLineBeforeCloseParenthesisInMultilineList> </GeneratorOptions> <PrimaryKeyName>PK_%TABLENAME%</PrimaryKeyName> </Settings>

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 :)


* indicates required

Please select all the ways you would like to hear from Agile Sql Club:

You can unsubscribe at any time by clicking the link in the footer of our emails. For information about our privacy practices, please visit our website.

We use Mailchimp as our marketing platform. By clicking below to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing. Learn more about Mailchimp's privacy practices here.