T-SQL Unique Identity Column as Part of Composite Primary Key

Question

Suppose that you have a table with the following structure:

CREATE TABLE [log] (
  [type] int NOT NULL,
  [stat] nvarchar(20) NOT NULL,
  [id] int IDENTITY (1, 1) NOT NULL,
  descr nvarchar(20),
  PRIMARY KEY ([type], [stat], [id])
)

Is it possible to force the [id] to be incremented only whenever the other two PK fields have the same values, and not independently as is now? For instance:

type    stat      id     descr
5       ERROR     1      Test  <---
3       WARNING   1      Test
5       ERROR     2      Test  <---
2       ERROR     1      Test
1       WARNING   1      Test
5       WARNING   1      Test
5       ERROR     3      Test  <---

Use trigger for increment http://stackoverflow.com/questions/3583973/using-a-trigger-to-simulate-a-second-identity-column-in-sql-server-2005 — Recursive, Oct 09 '14 at 11:25
@Hannobo, I don't think so. The whole point is to generate *new* IDs. I don't see how we could do this only with computed columns. Even if, it will probably quickly become a performance issue. — ax1mx2, May 24 '20 at 05:27

score 4 · Accepted Answer · answered Oct 09 '14 at 11:27

No. The purpose of an IDENTITY (or SEQUENCE) is only to generate an incremental integer. There may be gaps as values are not reused, and values may be reserved but not used.

You can use an expression in queries to show the desired value.

ROW_NUMBER() OVER (PARTITION BY type, stat ORDER BY id) AS Seq

score 1 · Answer 2 · answered Oct 09 '14 at 11:42

This i think would get your job done

CREATE TABLE [LOG1] (
  [TYPE] INT NOT NULL,
  [STAT] NVARCHAR(20) NOT NULL,
  [ID] INT ,
  DESCR NVARCHAR(20),
  PRIMARY KEY ([TYPE], [STAT], [ID])
)

CREATE TRIGGER TR_LOG
ON [DBO].[LOG1]
INSTEAD OF INSERT
AS
 BEGIN
DECLARE @CNT INT=0
IF EXISTS(SELECT 'X' FROM LOG1 A JOIN INSERTED B ON A.TYPE=B.TYPE AND A.STAT=B.STAT)
SET @CNT=(SELECT COUNT(*) FROM LOG1 A JOIN INSERTED B ON A.TYPE=B.TYPE AND A.STAT=B.STAT)
PRINT @CNT
INSERT INTO LOG1(TYPE,STAT,ID,DESCR) 
SELECT TYPE,STAT,ID+@CNT,DESCR FROM INSERTED
END

paparazzo · Answer 3 · 2014-10-11T12:25:07.873

0

That is not 3NF
I would fix it with data design

CREATE TABLE [logBase] (
  [id] int IDENTITY (1, 1) NOT NULL,
  PRIMARY KEY ([id])
)

CREATE TABLE [status] (
  [id] int IDENTITY (1, 1) NOT NULL,
  descr nvarchar(20),
  PRIMARY KEY ([id])
)

CREATE TABLE [log] (
  [type] int NOT NULL,
  [statusID] nvarchar(20) NOT NULL,
  [baseID] int NOT null, 
  descr nvarchar(20),
  PRIMARY KEY ([type], [statusID], [baseID])
)

edited Oct 11 '14 at 12:25

answered Oct 09 '14 at 11:32

paparazzo

44,497
23
105
176

1

The stated key has three columns, and the example data confirms that the only other possible key would be all 4; assuming descr can be anything, the 3 form the only key and the table is in 5nf. Moreover the constraint as stated is a transition constraint and does not exclude any particular table value. Re your design: baseId is missing in log; introducing statusID has nothing to do with normalization; you are missing FK from log statusID to status id and I suppose log baseId to logBase id; and it's not clear why logBase & baseID. – philipxy Oct 11 '14 at 04:58
@philipxy PRIMARY KEY ([type], [statusID], [baseID]) – paparazzo Oct 11 '14 at 12:24
2

@Blam, On what basis are you saying that the original design is "not 3NF"? There appears to be nothing obviously wrong with it as far as 3NF is concerned. – nvogel Oct 12 '14 at 08:32
@philipxy For one repeating information in stat. Let it go. – paparazzo Oct 12 '14 at 11:23
1

Nothing to do with 3NF then. – nvogel Oct 12 '14 at 16:38
1

There is no basis to claim the original design is not in 5NF let alone 3NF. Substitution of ids for values has nothing to do with normalization. – philipxy Oct 14 '14 at 18:44
@philipxy So a column state as in the US. A char field that could enter anything you would call that normalized? I want to vacation in Varcas - man no flights to Varcas. Is xNF stopping me from my dream vacation? – paparazzo Oct 15 '14 at 02:29

T-SQL Unique Identity Column as Part of Composite Primary Key

3 Answers3