auto increment on composite primary key

Question

I have a table called 'Workspaces' where the columns 'AreaID' and 'SurfaceID' work as a composite primary key. The AreaID references to another table called 'Areas' which only has AreaID as the primary key. What I want to do now is to make the surfaceID recound from 1 on every new AreaID. Right now I'm using the following code for the tables 'Areas' and 'Workspaces':

--Table 'Areas'
CREATE TABLE Areas (
AreaID INT IDENTITY(1,1) PRIMARY KEY,
Areaname VARCHAR(60) UNIQUE NOT NULL
)

--Table 'Workspaces'
CREATE TABLE Workspaces (
AreaID INT
CONSTRAINT ck_a_areaid REFERENCES Areas(AreaID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
SurfaceID INT IDENTITY(1,1)
CONSTRAINT ck_surfaceid CHECK (surfaceid > 0 AND surfaceid < 1001),
Description VARCHAR(300) NOT NULL,
CONSTRAINT ck_workspaces PRIMARY KEY (AreaID, SurfaceID)
)

When I use the code above I get a result like this when creating new workspaces in different areas:

AreaID    SurfaceID
1         1
1         2
1         3
2         4
2         5
3         6
Etc...

But I want the SurfaceID to recount from 1 on every new areaID, so my desired result would be like this:

AreaID    SurfaceID
1         1
1         2
1         3
2         1
2         2
3         1
Etc...

Does anyone know how this can be fixed?

Answer 1

You cannot easily do what you want. You can do it using triggers, but that is a rather ugly solution. You can get close to what you want by using a single identity primary key and then calculating the number you want on output:

CREATE TABLE Workspaces (
    WorkspacesId int not null identity(1, 1) primary key,
    AreaID INT,
    Description VARCHAR(300) NOT NULL,
    CONSTRAINT ck_a_areaid REFERENCES Areas(AreaID) ON DELETE CASCADE ON UPDATE NO ACTION,
);

Then when you query (or in a view):

select w.*, row_number() over (partition by areaId
                               order by WorkspaceId) as SurfaceId
from Workspaces

Note: This does not check the maximum value of surfaceId. If you really need to implement this logic, then you will need to use triggers.

Answer 2

Here is the solution that works with Multiple Rows.

Thanks to jFun for the work done for the single row insert, but the trigger is not really safe to use like that.

OK, Assuming this table:

create table TestingTransactions (
   id int identity,
   transactionNo int null,
   contract_id int not null,
   Data1 varchar(10) null, 
   Data2 varchar(10) null
);

In my case I needed "transactionNo" to always have the correct next value for each CONTRACT. Important for me in a legacy financial system is that there are no gaps in the transactionNo numbers.

So, we need the following trigger for to ensure referential integrity for the transactionNo column.

CREATE TRIGGER dbo.Trigger_TransactionNo_Integrity 
   ON  dbo.TestingTransactions 
   INSTEAD OF INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Discard any incoming transactionNo's and ensure the correct one is used.
    WITH trans 
         AS (SELECT F.*, 
                    Row_number() 
                      OVER ( 
                        ORDER BY contract_id) AS RowNum, 
                    A.* 
             FROM   inserted F 
                    CROSS apply (SELECT Isnull(Max(transactionno), 0) AS 
                                        LastTransaction 
                                 FROM   dbo.testingtransactions 
                                 WHERE  contract_id = F.contract_id) A), 
         newtrans 
         AS (SELECT T.*, 
                    NT.minrowforcontract, 
                    ( 1 + lasttransaction + ( rownum - NT.minrowforcontract ) ) AS 
                       NewTransactionNo 
             FROM   trans t 
                    CROSS apply (SELECT Min(rownum) AS MinRowForContract 
                                 FROM   trans 
                                 WHERE  T.contract_id = contract_id) NT) 
    INSERT INTO dbo.testingtransactions 
    SELECT Isnull(newtransactionno, 1) AS TransactionNo, 
           contract_id, 
           data1, 
           data2 
    FROM   newtrans 
END
GO

OK, I'll admit this is a pretty complex trigger with just about every trick in the book here, but this version should work all the way back to SQL 2005. The script utilises 2 CTE's, 2 cross applies and a Row_Num() over to work out the correct "next" TransactionNo for all of the rows in Inserted.

It works using an instead of insert trigger and discards any incoming transactionNo and replaces them with the "NEXT" transactionNo.

So, we can now run these updates:

delete from dbo.TestingTransactions
insert into dbo.TestingTransactions (transactionNo, Contract_id, Data1)
values (7,213123,'Blah')

insert into dbo.TestingTransactions (transactionNo, Contract_id, Data2)
values (7,333333,'Blah Blah')

insert into dbo.TestingTransactions (transactionNo, Contract_id, Data1)
values (333,333333,'Blah Blah')

insert into dbo.TestingTransactions (transactionNo, Contract_id, Data2)
select 333  ,333333,'Blah Blah' UNION All
select 99999,44443,'Blah Blah' UNION All
select 22,   44443 ,'1' UNION All
select 29,   44443 ,'2' UNION All
select 1,    44443 ,'3'

select * from dbo.TestingTransactions
order by Contract_id,TransactionNo

We are updating single rows, and multiple rows with mixed contract numbers - but the correct TransactionNo overrides the passed in value and we get the expected result:

id  transactionNo  contract_id  Data1       Data2
117             1        44443  NULL        Blah Blah
118             2        44443  NULL        1
119             3        44443  NULL        2
120             4        44443  NULL        3
114             1       213123  Blah        NULL
115             1       333333  NULL        Blah Blah
116             2       333333  Blah Blah   NULL
121             3       333333  NULL        Blah Blah

I am interested in people's opinions regarding concurrency. I am pretty certain the two CTEs will be treated as a single pass so, I am 99.99% certain the referential integrity will always be maintained.

Answer 3

I am agree with Mr. Linoff's answer but if you want to store it phisicaly, you can do it within an insert trigger:

Update Your_Table
set SurfaceID =  ( select max(isnull(SurfaceID,0))+1 as max 
                  from Workspaces t
                  where t.AreaID = INSERTED.AreaID )

---

EDIT:*(as an example wanted for how to implement it)

In the question I have seen two table that's why I have wrote the code as above, but following is a sample for what I meant:

Sample table:

CREATE TABLE testTbl 
(
    AreaID INT,
    SurfaceID INT, --we want this to be auto increment per specific AreaID 
    Dsc VARCHAR(60)NOT NULL
)

Trigger:

CREATE TRIGGER TRG
ON testTbl
INSTEAD OF INSERT

AS

DECLARE @sid INT
DECLARE @iid INT
DECLARE @dsc VARCHAR(60)

SELECT @iid=AreaID FROM INSERTED
SELECT @dsc=DSC FROM INSERTED

--check if inserted AreaID exists in table -for setting SurfaceID
IF NOT EXISTS (SELECT * FROM testTbl WHERE AreaID=@iid)
SET @sid=1
ELSE
SET @sid=(  SELECT MAX(T.SurfaceID)+1 
            FROM testTbl T
            WHERE T.AreaID=@Iid
          )

INSERT INTO testTbl (AreaID,SurfaceID,Dsc)
            VALUES  (@iid,@sid,@dsc)

Insert:

INSERT INTO testTbl(AreaID,Dsc) VALUES (1,'V1');
INSERT INTO testTbl(AreaID,Dsc) VALUES (1,'V2');
INSERT INTO testTbl(AreaID,Dsc) VALUES (1,'V3');
INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V4');
INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V5');
INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V6');
INSERT INTO testTbl(AreaID,Dsc) VALUES (2,'V7');
INSERT INTO testTbl(AreaID,Dsc) VALUES (3,'V8');
INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V9');
INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V10');
INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V11');
INSERT INTO testTbl(AreaID,Dsc) VALUES (4,'V12');

Check the values:

SELECT * FROM testTbl

Output:

AreaID  SurfaceID   Dsc
   1       1        V1
   1       2        V2
   1       3        V3
   2       1        V4
   2       2        V5
   2       3        V6
   2       4        V7
   3       1        V8
   4       1        V9
   4       2        V10
   4       3        V11
   4       4        V12

IMPORTANT NOTICE: this trigger does not handle multi row insertion once and it is needed to insert single record once like the example. for handling multi record insertion it needs to change the body of and use row_number