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?

Lakeishalakeland

auto-increment can't be used like this. – Autosome

You can use another table to track the last surface id for each area, then use a trigger to manage it. – Sperling

Ok, I'm pretty new at this so could you please give some code sample? – Lakeishalakeland

why do you want to do this? Integer IDs shoudl be meaningless, so what is the difference if you just use the autonumber? Why do you need it to restart the numbering, this is something that is VERY risky and can cause racec conditions and data integrity errors if you don't do it exactly correctly. This is a requirement I would push back to the person who gave it to me 100% of the time. You are wasting your processing time every day for something that should not be needed. The only time I woud accept it as a requirement is if it was a legal requirement. – Pincas

Answer 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 =  ( 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

Equerry

For performance reasons, is it better to store that information in a separate table instead of using max? – Sperling

as I don't know how is your schema structure and what is the estimated capacity of data, I cant give you an exact answer, but it's better to ask Mr. Linoff he is the king of data mining and database knowledge and knows better than me. (if has time and give answer!!!) – Equerry

You can't update the inserted virtual table. You would need to update the base table. You would most likely need to use an instead of trigger for this. And of course this type of solution starts getting into concurrency issues. – Domineer

@SeanLange, anyone know that UPDATE TABLE SET TABLE=... is invalid, It was just a mistake and typo, thanks a lot for your comment, it corrected, however I just meant the idea, and I have prefer the other answer. – Equerry

@Farhęg: I'm new to this, so could you please give a full trigger code example? – Lakeishalakeland

I will point out that triggers are only for experienced SQL developers and that if you choose to go this route, you are in great danger of causing problems down the road if you don't know what you are doing. Also , you should under no circumstances ever write a trigger that only handles one record inserts. That way lies data integrity issues and serious problems. – Pincas

@Pincas I already mentioned them all, and the example was just for giving what I meant. – Equerry

@xerzina, someone downvoted my answer without any comment or acceptable reason, can you please upvote it if you think was helpful, as you see I created the trigger you wanted – Equerry

I'm testing your code right now, the recount on surfaceid works, but somehow it inserts NULL in the other columns, that is the columns that are beyond the areaID and SurfaceID, why is this? – Lakeishalakeland

I don't know exactly how you customized it but did you select value for those columns you told are null, inside the trigger? – Equerry

It works! Forgot to select values for all columns! Thank you so much! :-D – Lakeishalakeland

If two records inserted in same INSERT then this fails. Can we solve that somehow? – Credenza

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.

Peculium

Answer 3

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.

Hindustani