Prevent Duplicate Entries in SQL Application

I have an application that’s running on two different machines. The use of the application is pretty simple, we scan a product and it associates the product_id and creates a Unique_ID that’s auto-incremental.

Ex: U00001 then the next is U00002

My problem is while both the machines are running, sometimes the Unique_ID is the same for two different products. It’s like the creation of the Unique_ID happens at the same time so it duplicates the entry.

What’s the best approach for this? Is it a connection problem?

Answer

You need a SEQUENCE or IDENTITY column, and then a computed column concatenates the U onto it

CREATE TABLE YourTable (
  ID int IDENTITY PRIMARY KEY,
  product_id varchar(30),
  Unique_ID AS FORMAT(ID, '"U"0000)
)

Or

CREATE SEQUENCE YourTable_IDs AS int START WITH 1 INCREMENT BY 1 MAXVALUE 9999;

CREATE TABLE YourTable (
  ID int PRIMARY KEY DEFAULT (NEXT VALUE FOR YourTable_IDs),
  product_id varchar(30),
  Unique_ID AS FORMAT(ID, '"U"0000)
)