Recently I was talking with someone who had not named any of the primary keys (PKs) in their database. They used system generated names and when they ran comparisons, they got all sorts of drops and creates they didn’t expect. This post shows how easy it is to declare PKs with names, even with complexity.
The Scenario
I’m going to use a schema that I have for baseball data. It illustrates the point well, I think.
For my friend, imagine a table like this one:
CREATE TABLE [dbo].[salaries]( [yearID] [int] NOT NULL, [teamID] [varchar](3) NOT NULL, [lgID] [varchar](2) NOT NULL, [playerID] [varchar](9) NOT NULL, [salary] [int] NULL, PRIMARY KEY CLUSTERED ( [yearID] ASC, [teamID] ASC, [lgID] ASC, [playerID] ASC ) )
If I create this table, the system will decide what the PK is. In fact, after I run this, I can see in sys.objects that the name is some random code.
What’s worse is that if this were an FK and I were dropping these in some script, I’d have issues in other systems. This name is different on each system.
The better solution is to make a simple change. Before the Primary Key keyword, I can add Constraint and a name, like this:
CREATE TABLE [dbo].[salaries]( [yearID] [int] NOT NULL, [teamID] [varchar](3) NOT NULL, [lgID] [varchar](2) NOT NULL, [playerID] [varchar](9) NOT NULL, [salary] [int] NULL, CONSTRAINT salariesPK PRIMARY KEY CLUSTERED ( [yearID] ASC, [teamID] ASC, [lgID] ASC, [playerID] ASC ) )
If I run this, then I have a better named PK.
Be explicit in your work. It makes for better code and easier, repeatable, reliable deployments.
SQL New Blogger
This is a simple thing, a code smell, but one that wastes DBA and developer time. This post shows a simple thing you can do to have better code. This took me about 10 minutes and you could do something similar.
Write this and maybe someone asks you how to do this in an interview.