0

How would you prevent users to create duplicate primary keys? My question is worded quite poorly so ill give an example to make it more clear (not sure how to put this in better words).

Let's say there are two users who are both trying to insert an item into our db. Now the ID (primary key) for this item is auto-incremented by 1 whenever a new item is inserted.

When two users try to insert an item into the db at the same time, the moment they insert the item, both items would be assigned with same ID but with different details and hence would cause a problem later when we look up that item b/c now there are two items with same ID.

How would you prevent this from Oracle? Please comment if my question is not clear!

Peter Chung
  • 1
  • 1
  • 1
  • 2
    Possible duplicate of [How to create id with AUTO\_INCREMENT on Oracle?](http://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle) – Paul Abbott Jun 15 '16 at 16:17
  • 4
    Unless you are building your own logic for generating the primary key which is not compatible with multiple sessions, this shouldn't be a problem. Use a sequence and Oracle will take care of it. – Justin Cave Jun 15 '16 at 16:19
  • @PaulAbbott No I guess my question was not clear. I was wondering if Oracle handles the case where two data is inserted at the same time (which I would assume that it would lead to a duplicate primary key if i were to use sequence (auto-increment) from oracle) – Peter Chung Jun 15 '16 at 16:20
  • @JustinCave great to know, just wanted to make sure, thank you! – Peter Chung Jun 15 '16 at 16:20
  • 1
    Make sure to have a unique constraint (e.g. a primary key constraint) on the column, and use a sequence to ensure unique values are generated. – Jeffrey Kemp Jun 16 '16 at 05:23
  • For error free inserts, sequences are the way to go, but you will not get gap-free primary key. This is a questionable goal. – Scott Jun 20 '16 at 01:49

0 Answers0