Per-entity autoincrement field in SQL Server?

Question

I have a table, "foo", in my database with primary key "fooID."

I have a second table, "fooAttributes," with a foreign key that references foo.fooID.

I'd like to have a composite key on the fooAttributes table (fooID, attributeNumber), where attributeNumber increments automagically on a per-foo basis when I insert a new attribute. So foo number 1 would have attributes 1, 2, 3... and foo number 2 would have attributes 1, 2, 3... etc.

What kind of SQL-fu is necessary to accomplish this? Is it practical? (I'm sure that it's possible, given enough effort.)

Answer 1

I don't think there's a way to do this automatically within SQL like an identity column in MS SQL Server. I would build a stored procedure to hande this passing in the fooID. For example:

begin tran    
select @attributeNumber = isnull(max(attributeNumber),0)+1 
from fooAttributes where fooID = @fooID 

insert fooAttributes(fooID, attributeNumber)
values(@fooID, @attributeNumber)
commit

Probably not what you wanted. If the automatic way is possible I'd like to see it as well.

Answer 2

Sounds like you want a Trigger on Foo.

On insert into Foo:

Insert fooAttributes
 select SCOPE_IDENTITY(), attributeId 
 from attributes

I'm assuming that you have a table called "attributes" that contains all the attributes. If not, you can do your own thing to arrive at the attribute Ids (a cross join on a subselect would be my first guess).