Anyone know if MS-SQL Server supports IDENTITY columns that are incremented for each new value of the column it depends on.
Let's say I have a client table with a ClientID column as it's PRIMARY KEY.
This column can be an auto-incrementing IDENTITY column.
Then I have an orders table. The PRIMARY KEY for the orders table is composed of (ClientID, OrderID). I would like the OrderID to be an IDENTITY field that increments by an arbitrary value (1 in this case) for every new value of ClientID...therefore creating a unique PRIMARY KEY.
MySQL (and maybe other RDMS's...I haven't checked) seems to do this automatically when you set a column as AUTOINCREMENT and then define a composite PRIMARY KEY on two fields.
I know this can be done manually using triggers, but I was wondering if there was a better way...
Yes, you can do this using triggers, but it seems as if you are going to be using these values and their order as part of your application logic. That's generally not a good idea. An autoincrementing ID is a surrogate key, and should not have any inherent relationship to the data it represents.
What are you planning to use these values for?
If it's not practically useful, then it's practically useless.
In an INSERT into the Orders table, I would supply the ClientID (and all the other fields I didn't mention in the orginal post) and the OrderID "for that client" would be incremented. Maybe you thought I meant that in the orders table the ClientID and OrderID are IDENTITY columns...
oh ye of little faith, yes, it certainly can (as afx2029 so nicely illustrated)
mysql can do several things that make sense when you see them
other databases could learn from some of what they're doing
and before y'all jump all over me, i did not say mysql was better than other databases, just that they actually do some neat things
i'm still undecided about whether i like the ability to store 2004-05-00 and 2002-00-00 as perfectly valid datetime values (there's a good reason for it), but i am aghast at them allowing 2003-02-29 and 2001-02-31
GDMI, to allow you to use a single date column to record an event (birthday, battle, etc.) and be able to store partial information, e.g. if all you know about great-great-grand-uncle Fritz is that he was born in 1903 but you don't know the month or year, put 1903-00-00
you can't do that with a "normal" date column in other databases, which require an exact date, so you'd either have to carry separate year, month, day columns and allow nulls, or else fake it by putting 1903-01-01 (which, i hasten to point out, is wrong and misleading)
blindman, i've sworn off marriage, having gone through two of them