Auto Increment On Composite Primary Key

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

Solution 1:

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 =  ( selectmax(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:

CREATETABLE testTbl 
(
    AreaID INT,
    SurfaceID INT, --we want this to be auto increment per specific AreaID 
    Dsc VARCHAR(60)NOTNULL
)

Trigger:

CREATETRIGGER TRG
ON testTbl
INSTEAD OFINSERTASDECLARE@sidINTDECLARE@iidINTDECLARE@dscVARCHAR(60)

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

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

INSERTINTO 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

Solution 2:

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:

CREATETABLE Workspaces (
    WorkspacesId intnotnullidentity(1, 1) primary key,
    AreaID INT,
    Description VARCHAR(300) NOTNULL,
    CONSTRAINT ck_a_areaid REFERENCES Areas(AreaID) ONDELETE CASCADE ONUPDATENO ACTION,
);

Then when you query (or in a view):

select w.*, row_number() over (partitionby areaId
                               orderby 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.

Solution 3:

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:

createtable TestingTransactions (
   id intidentity,
   transactionNo intnull,
   contract_id intnotnull,
   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.

CREATETRIGGER dbo.Trigger_TransactionNo_Integrity 
   ON  dbo.TestingTransactions 
   INSTEAD OFINSERTASBEGIN-- 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 ( 
                        ORDERBY 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 (SELECTMin(rownum) AS MinRowForContract 
                                 FROM   trans 
                                 WHERE  T.contract_id = contract_id) NT) 
    INSERTINTO 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:

deletefrom dbo.TestingTransactions
insertinto dbo.TestingTransactions (transactionNo, Contract_id, Data1)
values (7,213123,'Blah')

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

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

insertinto dbo.TestingTransactions (transactionNo, Contract_id, Data2)
select333  ,333333,'Blah Blah'UNIONAllselect99999,44443,'Blah Blah'UNIONAllselect22,   44443 ,'1'UNIONAllselect29,   44443 ,'2'UNIONAllselect1,    44443 ,'3'select*from dbo.TestingTransactions
orderby 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
117144443NULL        Blah Blah
118244443NULL1119344443NULL2120444443NULL31141213123  Blah        NULL1151333333NULL        Blah Blah
1162333333  Blah Blah   NULL1213333333NULL        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.