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