Multi-user environment, prevent PK violation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In a multi-user environment, how would I prevent users from adding Orders
with identical OrderID. For instance I have following tables in my database

Order
==================
OrderID Identity field
CustomerID
OrderDate
DeliveryAddress

OrderDetail
==================
OrderID
ProductID
UnitPrice
Quantity

Now, User A and User B view all the order placed by a customer. OrderID in
the DataSet would have AutoIncrement to True. When User A and User B add a
new order they both will have identical OrderID in the DataSet and add method
will fail for one of the user because of Primary Key violation constraint.

How can I prevent this from happening, so that it assigns a different
OrderID without generating an error?
 
One quick way it to assign the column in the database as an identity column
and let the DB engine auto-increment that number there.

Depending on what you want to do and how you want the number formatted this
may or may not work for you. One thing I have done in the past was use a
composite value as the PK in an order table. Each order entry person had a
specific numeric prefix assigned to them and then the DB was in charge of
incrementing a field to provide a unique number for each order entered. Once
the order was saved the real order number referenced throughout the rest of
the system was a composite of the order entry persons unique prefix and then
the sequential number that the DB assigned to that particular entry.

....but you mileage may vary..

There is always a way to do something, sometimes you just need to fiddle
around and find the way that is best for your case.

You might also decide to keep a table of available order numbers populated
ahead of time and just have a system that takes a request and assigns one of
them to each request.
 
Back
Top