Dependant IDENTITY columns

Anyone know if MS-SQL Server supports IDENTITY columns that are incremented for each new value of the column it depends on.

For exameple:
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.

The contents of the table would look like this

ClientID OrderID
--- ---
1 0
1 1
2 0
1 2
2 1
2 2
1 3

and so on...

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

Thanks in advanceYou are only allowed 1 IDENTITY Column per table...

How do you plan to INSERT the data in to the table?|||MySQL will do that? I'm skeptical...

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?|||I know.

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...|||BlindMan:
I wasn't planning on using it for anything (for now anyways), I was just curious. The Clients/Orders table was just a commonly used example.

As for it being possible with MySQL, I checked it again (since I first discovered this a while back), and yes it is possible. The syntax under MySQL would roughly be:

CREATE TABLE Clients (
ClientID INT AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE Orders (
ClientID INT,
OrderID INT AUTO_INCREMENT,
CONSTRAINT PK_SomeName PRIMARY KEY (ClientID, OrderID));

Obviously there would be a foreign key set on Orders.ClientID...but I just want to show the example..

After populating Clients with a few values, you can insert a few into orders
like so:

INSERT INTO Orders (ClientID) VALUES (SomeValues);

and the OrderID field will increment like I mentionned in the original post,
except that the default AUTO_INCREMENT seed is "1", not "0".

Like I said, I was just curious if it was possible to use this with MS-SQL
without using a workaround.

Thanks for the information!!|||MySQL will do that? I'm skeptical...

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|||Rudy, what would be a good reason for allowing (2002-00-00) or (2004-05-00 ) as valid datetime?|||Yeah Rudy! If you love MySQL so much, why don't you just MARRY it? Huh? Well, why dontcha? If it's so much BETTER? :p|||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

:cool:|||Rudy, Thanks for the explanation. I admit I haven't thought about that before ...|||Rudy, Thanks for the explanation. I admit I haven't thought about that before ...

I can't tell. Is he talking about your code or your marriages? I quit after one, so I guess that makes me a more efficient marriager than you. ;)|||I can't tell. Is he talking about your code or your marriages? I quit after one, so I guess that makes me a more efficient marriager than you. ;)Everybody should do it at least once. It helps promote the species, and it instills a real fear of the potential of hell.

This coming from a guy who's never found an eligible woman smart enough to be interesting and dumb enough to say "yes".

-PatP|||hell hath no fury like a woman with a divorce lawyer

but it is worth the pain

i have two fantastic boys from my first marriage, 32, and 30

and a fabulous daughter, 13, and son, 11, from my second

i would love to have even more kids, but i am broke, out of work, and too old and unattractive to have any hope of a third marriage

my only hope now is that one of my older boys has grandkids soon|||I can't tell. Is he talking about your code or your marriages?

Nope! I was talking about his code! ;)

But speaking about marriages .. I am preparing for my marriage .. hopefully it will be my first and last eventhough statistics are against that.

Blindman, Rudy you guys are great SQL consultants ;) in a "SQL World" that alone should qualify to get chicks! :p|||Careful, GDMI. Statistics shouw that just over half of the marriages in the US end in divorce. But just under half end in death!|||Nope! I was talking about his code! ;)

But speaking about marriages .. I am preparing for my marriage .. hopefully it will be my first and last eventhough statistics are against that.

Blindman, Rudy you guys are great SQL consultants ;) in a "SQL World" that alone should qualify to get chicks! :p

What IT offices have you been working in?|||a long time ago, i knew a woman who knew sql...

$expletive, was she ever hot!!!!!!!!!!

in my experience, IT has as many attractive women as any other vocation|||hell hath no fury like a woman with a divorce lawyer

but it is worth the pain

i have two fantastic boys from my first marriage, 32, and 30

and a fabulous daughter, 13, and son, 11, from my second

i would love to have even more kids, but i am broke, out of work, and too old and unattractive to have any hope of a third marriage

my only hope now is that one of my older boys has grandkids soonGood kids do help to ease the pain!

I can't comment on the "broke" part, and I don't see you as "unemployed" although I know that you'd debate that point. I'm not qualified to comment on unattractive (I only pay attention to females on that point), but somehow I still can't buy that one either.

Grandkids are great fun! They are even more fun than the first time around, and at least in my opinion give you your first real chance to "check your work"

-PatP|||I don't mean to be off-topic or anything....but is what I originally posted
possible with MS-SQL Server without resorting to triggers or another workaround?

:)|||Anyone know if MS-SQL Server supports IDENTITY columns that are incremented for each new value of the column it depends on.

For exameple:
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.

The contents of the table would look like this

ClientID OrderID
--- ---
1 0
1 1
2 0
1 2
2 1
2 2
1 3

and so on...

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

Thanks in advance

No

I asked you how you plan to do the INSERT.

If you tell us, we might be able to suggest something...|||I don't mean to be off-topic or anything....but is what I originally posted
possible with MS-SQL Server without resorting to triggers or another workaround?

:)As Brett answered (twice), no. No truely relational database can support what you are looking for, since it is application specific and violates the single domain rule of first normal form as expressed in relational algebra.

The only way to add application specific code to SQL Server is via a trigger.

-PatP|||No truely relational database can support what you are looking for ...mysql can do it

or is that why you were so careful to qualify your statement? ;)

...since it is application specific and violates the single domain rule of first normal form as expressed in relational algebra.stop that! go to your room!

it's a feature

;)|||Just like

SELECT + 'This is a feature?'

Is a feature|||stop that! go to your room!

it's a feature

;)I just said that it wasn't relational, and pointed out how the feature could be simulated in a relational database. Was I naughty?

Just like

SELECT + 'This is a feature?'

Is a featureI see it that way!

-PatP