July 11, 2006 at 8:43 pm
Hi,
I'm currently writing a small application that is using SQL Server as a back-end database. A part of my database looks something lie the following:
Tables:
Orders(OrderId[int, PK], OrderDescription[varchar] etc...)
OrderLines(OrderId[int, PK, FK], OrderLineId[int, PK], etc...)
What I need to achieve is - everytime that a new line is inserted into an orderlines table part of the primary key will be the OrderId and the OrderLineId should be auto-incremented from 1 for each OrderId in the OrderLines table.
I know i can do this manually in my program, but i'm just wondering if theres a way to achive this in SQL Server?
Thanks,
Nick Goloborodko
July 12, 2006 at 1:22 am
Hi Nick,
You can use the identity property on the OrderLineId colum.
create table OrderLines
(OrderLineId int identity,
OrderId int,...etc)
Incidentally, if you're going to have OrderLineId increment by one then it is fair to say that this column will be unique in the table, so you might as well make it the primary key rather than have a composite primary key across OrderId and OrderLineId.
Hope that helps,
July 13, 2006 at 11:54 pm
Not quite right. That assumes that OrderLineID is a candidate key on its own for the table, which it's not.
Nick wants something like
Order OrderLineID
1 1
1 2
1 3
2 1
2 2
So the combo of Order + OrderLineID acts as the key. The OrderLines repeat for each order.
Since only one user will be inserting rows for a single order at any given time (ie you cannot have two users inserting the new rows for order 1 but you could have a user inserting rows for order 1 and another user inserting rows for order 2) you can easily do this with a MAX(orderLIneID) + 1 statement whilst grouping on the OrderID column.
EG
insert into orderLines(orderID, orderLineID, col1, col2)
select @NewOrderID, max(orderLineID) + 1, @Newcol1, @Newcol2
from OrderLines OL
where orderID = @newOrderID
group by orderID
that will do one record at a time. You can modify this to insert many records at a time with some creative table joins and subqueries to get the next max values.
July 14, 2006 at 8:33 am
I agree with Karl, OrderID can be any number but OrderLineID should be incremented by one for each OrderID added to the table
July 16, 2006 at 3:04 pm
Hi,
Thank for all of your replies, I think i will go with the Karl's sudgestion. I've got one further question: if i use a subquery to select the OrderLineId using the MAX function, will the whole query be executed in the context of the same transaction, or is there a possibility that (in a very high load enviroment) the max function will execute just a tiny fraction of a second faster and another row can be added before the INSERT can be performed?
Currently i have these statements in the Stored Procedure.
Thanks in advance,
Nick Goloborodko
July 17, 2006 at 1:00 am
Hi Nick,
I'm not sure I understand your question. If you're going to use an identity column then you don't need to worry about selecting the MAX(OrderLineId) - it's done automatically for you.
However, if you're going to select your own MAX(OrderLineId) then you'd need wrap your statements in a transaction and you'd need to play around with locking hints to ensure that the table is locked at the point at which you perform the select statement for the duration of the entire transaction.
July 18, 2006 at 4:37 am
Locking hints mightn't needed (although they certainly couldn't hurt) IFF there can only be one user modifying a given order at any given time. I assumed that you wanted the orderLineIDs to restart within each order, making the primary key in the orderLines table a composite of orderID and orderLineID. If you don't have to have the orderLineID restarting at 1 for each order then save yourself a lot of trouble and use an identity column.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply