Primary Key Max Length

I’m planning on fishing my first Muskie tournament this year. The Rhinelander Hodag Musky Challenge is held each year up in September on various lakes around Rhinelander. Based on results from the last several years catching two legal (40+ inch) muskies will put you into the top 3, and cash prizes are awarded to the top 10 finishers. This is a really a great opportunity for muskie fishermen because as long as your doing what you can correctly, anyone can get lucky twice in two days. On the other hand, it shows how challenging musky fishing can be – about 3 of the 80 or so teams will actually manage to land two of the fish.

Winners of the 2014 Rhinelander Toyota Hodag Musky Challenge (from the left) third place, Matt Rollins and Brad Rollins; first place, Scott Denzer and Kevin Douglas; Rhinelander Area Chamber of Commerce Executive Director Dana DeMet; second place, Jason Mollen and Todd Havel.

Winners of the 2014 Rhinelander Toyota Hodag Musky Challenge (from the left) third place, Matt Rollins and Brad Rollins; first place, Scott Denzer and Kevin Douglas; Rhinelander Area Chamber of Commerce Executive Director Dana DeMet; second place, Jason Mollen and Todd Havel.

One interesting thing about fishing tournaments is that many require you to release the fish after having it recorded by a judge. In fact, if the fish accidentally dies before you can successfully let it go you are often very harshly penalized. To help facilitate this requirement, a livewell with a minimum size is required. The Rhinelander Hodag Musky Challenge posts this in their list of rules:

ALL BOATS must have a live well or aerated
container measuring at least 40”. Wisconsin DNR
recommendation is 45”.

The reason for this is pretty simple – how can you expect to release a 40+ inch fish crammed into a 30 inch livewell? It’s probably not going to work.

Size is important, especially in SQL Server. One place this is particularly true is the size of the primary key. This page lists Microsoft’s size limit on various objects, and we can see the size limit on the primary key is 900 bytes. A primary key is one or more columns that must be unique within a table. Many times this will be a single numeric column such as an identity integer field. However, sometimes it makes sense to use other types of fields or a combination of several fields. The syntax for creating a table with a primary key is:

CREATE TABLE [dbo].[TournamentCatches]
    (
      [TC_ID] [INT] IDENTITY(1, 1) NOT NULL ,
      [Team_ID] [INT] NOT NULL ,
      [Lake_ID] [INT] NOT NULL ,
      [FishSpecies_ID] [INT] NOT NULL ,
      [FishLength] [NUMERIC](5, 2) NOT NULL ,
      [FishScore] [INT] NOT NULL ,
      [FishSurvived] [BIT] NOT NULL ,
      CONSTRAINT [PK_TournamentCatches] PRIMARY KEY ( [TC_ID] ASC )
    )
ON  [PRIMARY]

So what happens if we try to create a primary key on fields totaling more than 900 bytes? Let’s add a description of the catch to the table and try to include it in the primary key (this would usually not belong in a primary key, but for the sake of the example):

CREATE TABLE [dbo].[TournamentCatches]
    (
      [TC_ID] [INT] IDENTITY(1, 1) NOT NULL ,
      [Team_ID] [INT] NOT NULL ,
      [Lake_ID] [INT] NOT NULL ,
      [FishSpecies_ID] [INT] NOT NULL ,
      [FishLength] [NUMERIC](5, 2) NOT NULL ,
      [FishScore] [INT] NOT NULL ,
      [FishSurvived] [BIT] NOT NULL ,
      CatchDescription CHAR(900)
        CONSTRAINT [PK_TournamentCatches]
        PRIMARY KEY ( [TC_ID] ASC, CatchDescription ASC )
    )
ON  [PRIMARY]

20141230 PK Too Long

You can see SQL Server will not let us create this primary key because it’s too big. What if the CatchDescription was VARCHAR(1000) instead of CHAR(900)?

DROP TABLE dbo.TournamentCatches;

CREATE TABLE [dbo].[TournamentCatches]
    (
      [TC_ID] [INT] IDENTITY(1, 1) NOT NULL ,
      [Team_ID] [INT] NOT NULL ,
      [Lake_ID] [INT] NOT NULL ,
      [FishSpecies_ID] [INT] NOT NULL ,
      [FishLength] [NUMERIC](5, 2) NOT NULL ,
      [FishScore] [INT] NOT NULL ,
      [FishSurvived] [BIT] NOT NULL ,
      CatchDescription VARCHAR(1000)
        CONSTRAINT [PK_TournamentCatches]
        PRIMARY KEY ( [TC_ID] ASC, CatchDescription ASC )
    )
ON  [PRIMARY]

20141230 PK Variable Length

This time the primary key was successfully created. However, this has created a very dangerous situation. If someone tries to insert a value larger than 899 bytes into the CatchDescription or update an existing value in that field to a value larger than 899 bytes, SQL is going to stop them.

INSERT  INTO dbo.TournamentCatches
        ( Team_ID ,
          Lake_ID ,
          FishSpecies_ID ,
          FishLength ,
          FishScore ,
          FishSurvived ,
          CatchDescription
        )
VALUES  ( 12 ,
          3 ,
          1 ,
          43.5 ,
          56 ,
          1 ,
          REPLICATE('Fishing Rocks! ', 61) -- This will replicate these 15 characters 61 times for a total length of 915 bytes
        )

20141230 PK Variable Length Failed

This is a great example of the notion that just because SQL Server lets you do something doesn’t mean that you should.

Advertisements