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

Question

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

Answer 1

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.