Build and display a complex composite primary key in a column

Question

I need to build a primary key consisting of 3 sections.

• The first section is 3 letters long and must be an a followed by two digits eg "a00"
• The second section is a 3 digit unique identifier INCLUDING LEADING ZEROES, example "003"
• The third section is the year section of the date, eg "2008"

This is all separated with dashes, so a complete key would look like "a00-014-2008"

My solution is to hold three separate columns with the data for each, unformatted, then add the clustered primary key constraint and format it when I need to display it in a view. Is this a good way of going about it? I think there might be a way to make the whole key on update using a trigger but I don't know how. Also how would I concatenate and display it in a view?

Though not a homework question, this will help me academically.

Answer 1

My question is: do any of these three values have meaning by themselves? Or are they simply components of a primary key? If they have absolutely no other meaning then imho you are better off just creating a single column with the whole value.

Now "meaning" may be hard to qualify. It could mean that it's a particular customer code or something that is searched on or, well, lots of things.

Generally I try and avoid composite keys as they complicate joins and usually make your life difficult with ORMs (that typically view such practice - rightly or wrongly - as "legacy").

If those fields do have meaning then arguably they shouldn't be part of the primary key. I view such a practice as a mistake. I nearly always use auto number columns or sequences for primary key values (known as a "technical key" as opposed to a semantic key).

Given that the user supplies part of this and it needs to be formatted a particular way, I am assuming the key has a whole has a meaning, be it a product code, patient code, booking number or something like that. Would that be correct?

If so, given that and my almost complete preference for technical keys (opinions vary on this issue) I would consider not making this the primary key. Create an auto number field for that. Just make this as acolumn with a unique index that is varchar(12) or whatever.

Answer 2

CREATE TABLE mytable (
      first TINYINT NOT NULL CHECK(first BETWEEN 0 AND 99),
      second INT NOT NULL CHECK (second BETWEEN 0 AND 999),
      third INT NOT NULL,
      CONSTRAINT pk_mytable_123 PRIMARY KEY (first, second, third)
      )
GO
CREATE VIEW v_mytable AS
SELECT  'a' + RIGHT('00' + CAST(first AS VARCHAR), 2) AS first,
        RIGHT('0000' + CAST(second AS VARCHAR), 4) AS second,
        third
FROM    mytable
GO

INSERT
INTO    mytable
VALUES  (1, 1, 2008)
GO

SELECT  *
FROM    v_mytable
GO

first  second  third
----   ----    ----
a01    0001    2008

This primary key will hold only INT values, all formatting will be done in the view.

Note that to achive best results in terms of performance, you better create computed columns instead of the view, and create an additional UNIQUE index in these columns:

CREATE TABLE mytable (
      _first TINYINT NOT NULL CHECK(_first BETWEEN 0 AND 99),
      _second INT NOT NULL CHECK (_second BETWEEN 0 AND 999),
      third INT NOT NULL,
      first AS 'a' + RIGHT('00' + CAST(_first AS VARCHAR), 2),
      second AS RIGHT('0000' + CAST(_second AS VARCHAR), 4)
      CONSTRAINT pk_mytable_123 PRIMARY KEY (_first, _second, third)
      )

CREATE UNIQUE INDEX ux_mytable_123 ON mytable (first, second, third)

Answer 3

Primary keys traditionally have two purposes: to uniquely identify a row, and to allow other database tables to refer to it. Composite primary keys fulfill the first purpose, but make the second hard, because the other table has to refer to 3 columns. The same goes for ORM systems, or even updates from a web page.

A best practice solution is to make an identity column as primary key. This allows easy reference to the row, and is a scenario that all ORM layers can deal with. Then you add a "unique" index on the three columns that would make up the primary key. This way you have the best of both worlds.

Answer 4

hmm so, composite keys are a "dead end"?