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

Therefore for example the attributes (as a very cut down version) might look like this:

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

as data this could look like:

A[aPK, SomeFields]: 
1, Foo
2, Bar

B[bPK, aFK, SomeFields]:
1, 1, FooData1
2, 1, FooData2
1, 2, BarData1
2, 2, BarData2

C[cPK, bFK, aFK, SomeFields]:
1, 1, 1, FooData1More
2, 1, 1, FooData1More
1, 2, 1, FooData2More
2, 2, 1, FooData2More
1, 1, 2, BarData1More
2, 1, 2, BarData1More
1, 2, 2, BarData2More
2, 2, 2, BarData2More

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 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.