What is the correct way to increment a field making up part of a composite key

I have a bunch of tables whose primary key is made up of the foreign keys of other tables (Composite key).

Therefore the attributes (as a very cut down开发者_如何学Python version) might look like this, please note that I am only showing the attributes that make up the key - there would be and are other attributes that are stored alongside the composite key:

A[aPK] 1:M B[bPK, aFK] 1:M C[cPK, bFK, aFK]

as data this could look like:

A[aPK]: 
1
2

B[bPK, aFK]:
1, 1
2, 1
1, 2
2, 2

C[cPK, bFK, aFK]:
1, 1, 1
2, 1, 1
1, 2, 1
2, 2, 1
1, 1, 2
2, 1, 2
1, 2, 2
2, 2, 2

As you can see each of the rows are unique and although having the left most attribute set as an auto-number would look like this:

C[cPK, bFK, aFK]:
1, 1, 1
2, 1, 1
3, 2, 1
4, 2, 1
5, 1, 2
6, 1, 2
7, 2, 2
8, 2, 2

It would remove the need for the 2nd, and 3d column to be part of the composite key, which defeats the object and would give me a single auto-incremented primary key.

I've got this running in a MSSQL DBMS and I'm looking for the best way to increment the left most column, in each table when a new tuple is added to it.

I can't use the Auto Increment Identity Specification option as that has no idea that it is part of a composite key.

I also don't (think I) want to use any aggregate function such as: MAX(field)+1 as this will have adverse affects with multiple users inputting data, rolling back etc. There might however be a nice trigger based option here, but I'm not sure.

This must be a common issue so I'm hoping that someone has a lovely solution.

As a side which may or may not affect the answer, I'm using Entity Framework 1.0 as my ORM, within a c# MVC application.

I can't use the Auto Increment Identity Specification option as that has no idea that it is part of a composite key.

It's unique; it doesn't have to be part of a compound key. But the other two columns should be a compound foreign key, as in (bFK, aFK) references B (bPK, aFK).

You can declare cPK to be the primary key, and also declare an additional UNIQUE constraint on (cPK, bFK, aFK) if you need to.