Composite Primary Key with Auto Increment Column

Question

Hi, I want to create a table with a composite key. Column A references another table's identity column. Clumn B needs to be unique for a given value in ColumnA. The table would look like this:

 

ColumnA           ColumnB

50001                1

50001                2

50001                3

50002                1

50002                2

...

 

I cant seem to figure out how to create this fairly simple table. Any help you can give will be appreciated.

Answer 1

You have to manually manage the values for column B.  You can look at the following Example by Arnie Rowland that should provide an idea of how to go about this:

 

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=IncrementAlphaNumericValues&referringTitle=Home

 

Can you describe why you need the numbers order like you have them in ColumnB?  Somethings you might consider in implementing a composite primary key like this:

1.  By default this will create a composite clustered index.  This probably isn't going to be efficient for a majority of queries and you may need to change the clustered index to something that better suits your queries.

2.  As Arnie mentions in his article, concurrency issues may arise, so read his other article about Locking a Stored Procedure for single use.

Answer 2

 

From the small amount of detail, it appears that you want to sequentially number rows for a specific FK, and then use the ( FK + sequential number ) as a PK. This is 'doable'.

 

Common examples of this desired behavior include items (detail rows) on invoices, detail lines on a bill of laden, etc.

 

Frankly, the sequential numbering for this type of scheme  is 'best' done in the application. It can be burdensome to do it in the dataserver.

 

However, if you must attempt to accomplish it in T-SQL, I suggest the following approach:

 

Add a 'INSTEAD OF' INSERT trigger to the table.

• When a new row is inserted, get the MAX() ColumnB for ColumnA,
• Set = zero if nothing found,
• Then increment by 1,
• Add the new row to the table

You 'could' use a 'regular' FOR INSERT trigger, but you would have to set ColumnB to a default value of zero (0).

 

I think that this would be more 'robust' and not suffer from the concurrency issues discussed in the articles that Johathan mentioned.

 

 

 

Answer 3

Arnie, will the instead of trigger be safe?  It would be kind of tricky for concurrency purposes as well as multiple rows. I hate having an insert fail because it clashes with another (and the code to keep it from clashing can be bad for concurrency too.)

 

OP, in one regard I would ask if you really want to do this in the table, or if you mostly just need to be able to view the data like this.  So implement an identity column as a key, then have your index on the grouping value and the identity (possibly just the clustered on the identity) and then when viewing the data, you can display it with the order like you suggest easily using row_number()

 

The problem is maintaining this value, and likely maintaining it "gapless"  Most people want to see a value like this with no gaps, so you end up with lots of work to do on delete, especially if you delete a lot.

 

You could store the value if your really needed to and have a proc to reorganize the values for a grouping value on every insert (defaulted to 0 as Arnie suggests), update, or delete.  That proc could be made single threaded with an applock, but I still would suggest considering if you *really* need to view it like this, or if it really is a need to implement a value like this in the storage.

 

 

Answer 4

Questions about concurrency and 'safety' are good points to ask and explore.

 

As I laid out, IF the issue was something like sequencing Order items on an Invoice, AND IF there is only one user adding those order items for a specific invoice, then there 'should' not be a concurrency issue.

 

There is just not enough information provided to give an efficient and 'safe' suggested solution.

 

Answer 5

I was considering the route that Louis took with this:

 

Code Snippet

declare @example table

(rowid int identity primary key,

columnA int)

 

insert into @example select 50001

insert into @example select 50002

insert into @example select 50001

insert into @example select 50003

insert into @example select 50002

insert into @example select 50001

insert into @example select 50003

insert into @example select 50003

insert into @example select 50002

insert into @example select 50001

 

select columnA, rank()over (partition by columnA order by rowid asc) as columnB

from @example

 

 

 

which was why I asked, why they needed to store the data this way.  If it was just for display purposes, then the above example shows how it would be better to pull the data with this format over storing it this way.

Answer 6

I wasn't disagreeing with either of you   I completely agree that there isn't enough information.  I was just wondering what you had in mind for concurrency with the INSTEAD OF trigger method.  I couldn't think of a way to do it right off the top of my head but perhaps you did have.

 

I was just filling in details that I didn't see (not necesarily because they arent there, but sometimes I figure if enough of us ask the same sorts of questions in a different way it will sink in what we are asking.  I know that when I am first on the scene I usually find that someone will come in and "finish" the answer if I forgot something or wasn't completely clear. (certainly no disrespect intended, and I will try to make it clear if disrespect is intended

 

Thanks!

Answer 7

I realise this is quite an old question but I stumbled across this yesterday on my quest to do something similar and I reckon I've solved the concurrency issue.

Basically, I use sp_getapplock() and sp_releaseapplock() around the code in the INSTEAD OF INSERT trigger in order to ensure that nothing else adds anything to the table while the calculation of the new Id is taking place.

In my situation I had a parent / child table relationship. Tenant is the parent table, and Products the child. 

CREATE TRIGGER dbo.IOINS_Products 
   ON  dbo.Products 
   INSTEAD OF INSERT
AS 
BEGIN
  SET NOCOUNT ON;
  
  -- Acquire the lock so that no one else can generate a key at the same time.
  -- If the transaction fails then the lock will automatically be released.
  -- If the acquisition takes longer than 15 seconds an error is raised.
  DECLARE @res INT;
  EXEC @res = sp_getapplock @Resource = 'IOINS_Products', 
    @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = '15000',
    @DbPrincipal = 'public'
  IF (@res < 0)
  BEGIN
    RAISERROR('Unable to acquire lock to update Products table.', 16, 1);
  END

  -- Work out what the current maximum Ids are for each tenant that is being
  -- inserted in this operation.
  DECLARE @baseId TABLE(BaseId int, TenantId int);
  INSERT INTO @baseId
  SELECT MAX(ISNULL(p.Id, 0)) AS BaseId, i.TenantId 
  FROM  inserted i
  LEFT OUTER JOIN Products p ON i.TenantId = p.TenantId
  GROUP BY i.TenantId

  -- The replacement insert operation
  INSERT INTO Products
  SELECT 
    i.TenantId, 
    ROW_NUMBER() OVER(PARTITION BY i.TenantId ORDER BY i.TenantId) + b.BaseId 
      AS Id,
    Name
  FROM inserted i
  INNER JOIN @baseId b ON b.TenantId = i.TenantId

  -- Release the lock.
  EXEC @res = sp_releaseapplock @Resource = 'IOINS_Products', 
    @DbPrincipal = 'public', @LockOwner = 'Transaction'
END
GO

I've given it a fuller write up on my blog (which I can't currently link to because I've only just created my account): http://colinmackay.co.uk/2012/12/29/composite-primary-keys-including-identity-like-column/