How to create a custom auto generated ID number for a primary key column?

I have created this table and I inserted the following values:

CREATE TABLE Product
(
     ID INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
     Product_No AS RIGHT ('PDT0000' + CAST(ID AS VARCHAR(10)),10) PERSISTED,
     Product_Name VARCHAR(50) NOT NULL
)

INSERT INTO Product(Product_Name)
VALUES('Fish'), ('Shrimp'), ('Crab')

Then by using

SELECT * 
FROM Product

I get the following results:

ID  Product_No  Product_Name
1   PDT00001    Fish
2   PDT00002    Shrimp
3   PDT00003    Crab

As you can see that ID is the primary key column .. not Product_No … I want Product_No to be the primary key column here without requiring to create ID column. Therefore, this is the output I want to get with

SELECT * 
FROM Product     (with Product_No as the Primary Key):

Product_No  Product_Name
-------------------------
PDT00001    Fish
PDT00002    Shrimp
PDT00003    Crab

So this is what I have tried:

CREATE TABLE Product
(
     Product_No INTEGER IDENTITY(1,1) PRIMARY KEY ('PDT0000' + Product_No AS VARCHAR(10)),10),
     Product_Name VARCHAR(50) NOT NULL
)

This is the following error I got:

Incorrect syntax near ‘PDT0000’.

So, how do I fix this ?

It would be really helpful if the appropriate syntax solution is provided.

Answer

If you want to make Product_No the primary key – just use this SQL syntax:

CREATE TABLE Product
(
     ID INTEGER IDENTITY(1,1) NOT NULL
          CONSTRAINT UC_Product_ID UNIQUE,
     Product_No AS RIGHT ('PDT0000' + CAST(ID AS VARCHAR(10)), 10) PERSISTED 
          CONSTRAINT PK_Product PRIMARY KEY CLUSTERED,
     Product_Name VARCHAR(50) NOT NULL
)

Now your column Product_No is the primary key for this table. You cannot combine the IDENTITY value column with the computed column specification into a single column – that’s just not possible.

HOWEVER: even with this, and your sample data, this query:

SELECT * 
FROM Product

still returns this result:

ID  Product_No  Product_Name
------------------------------
1   PDT00001    Fish
2   PDT00002    Shrimp
3   PDT00003    Crab

But what is wrong with that output?? I don’t quite understand why this is a problem in your case?

If you don’t want (or create for) the ID column, just run this query:

SELECT Product_No, Product_Name
FROM Product

to get this result:

Product_No  Product_Name
------------------------------
PDT00001    Fish
PDT00002    Shrimp
PDT00003    Crab