ds2128629
2018-01-09 07:49 阅读 63
已采纳

SQL避免插入重复的新购物车ID

(First and foremost, sorry for my bad English, it is not my first language.)

I have a SQL database table to store some shopping cart, that likes something like this:

Shopcart
----------
ID(PK) shopcartid price user
1 S123 112.00 Tom
2 S124 6543.00 Mary
3 S125 456.00 James
4 S126 25.00 Peter

Since the database was not set by me, I don't have much knowledge with the internal designs, proprieties, etc.

As far as I know for the database design wise,
ID column,
Allow NULL: No
Type: int
(Is Identity): Yes
shopcartid column,
Allow NULL: No
Type: nvarchar(100)

There is also an clustered index set.
Name: PK_shopcart
Columns: ID(ASC),shopcartid(ASC)
Is Unique: Yes
Type: Primary Key

The website (in php) will create a shopping cart (INSERT INTO Shopcart shopcartid price user VALUES {new shopcartid} {price} {user}) once the user login to the site and if they don't already have a shopping cart assigned. What is a MUST is that the shopcartid to not duplicate. While ID is also unique, it should be auto-increment(?)(not sure). However, currently, the shopcartid keeps on duplicating as we add more shopping cart.

The way the site creates the shopcartid, originally was to use SELECT COUNT(*) AS value FROM Shopcart, and add 1 to the shopcartid, which most definitely don't work and will create duplicates.

What I proposed was to get the last shopcartid by getting the latest added shopping cart by ID which should be unique, and add 1 to that.:
SELECT shopcartid from Shopcart where ID = (SELECT MAX(ID) FROM Shopcart)

However, my colleague said it could still have possibility of creating duplicate shopcartid since with many concurrent users visiting the site, it could be possible that the fetch the same ID, thus the same shopcartid, and created a duplicated new shopcartid.

So here is my question, what is the best way to avoid creating a duplicate shopcartid, and how could this be achieved.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    douhunkuang8955 douhunkuang8955 2018-01-09 08:01

    Simply use a Sequencer for setting the ID of the shopping cart. Each time a user wants to create a new shopping cart, the sequenzer provides a new, unique ID.

    点赞 评论 复制链接分享
  • doufang1954 doufang1954 2018-01-09 08:53

    Use transactions to avoid duplication and Put unique key constraint on shopcartid to make sure that no duplicates are inserted in any case. .

    点赞 评论 复制链接分享

相关推荐