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

Question

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.

marc_s · Accepted Answer · 2018-08-05T16:17:42.490

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

. I think the `identity` column should still be declared `unique`. — Gordon Linoff, Aug 05 '18 at 15:33
@GordonLinoff: yes, good point. Updated my response (and also took the opportunity to give the primary key constraint an explicit name) — marc_s, Aug 05 '18 at 16:16

score 0 · Answer 2 · answered Aug 05 '18 at 11:15

Most probably you want product_no to not have gap in it. As you know when a transaction gets rollback, identity value jumps over that value. Therefore, if you want that Product_no to be sequential do NOT‌ use identity.
In addition having an extra column (ID) without any further use is annoying.
In SQL‌ Server 2017 afterward the concept of sequential has been introduced and you can make use of that.
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-2017

`SEQUENCE` was actually introduced with SQL Server **2012** already (not 2017) .... and the `SEQUENCE` object has the same issues with numbers being skipped as the `IDENTITY` has - depending on your configuration — marc_s, Aug 05 '18 at 12:59

score 0 · Answer 3 · answered Aug 05 '18 at 13:48

I want Product_No to be the primary key column here without requiring to create ID column.

Using a sequence you can eliminate the need to have the ID column. EG

--drop table if exists Product
--drop sequence seq_Product
go

create sequence seq_Product
start with 1
increment by 1 

go

CREATE TABLE Product
(
     Product_No varchar(20) PRIMARY KEY 
        default 'PDT' + format((next value for seq_Product),'00000' ),
     Product_Name VARCHAR(50) NOT NULL
)

insert into Product(Product_Name)
values ('Fish'),('Shrimp'),('Crab')



select * from Product

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

3 Answers3

Linked