CODEWITHSUNDEEP
sql-serverasp.net-mvc
user13853546
user13853546

Reputation:

How to custom my primary key value in mvc

I wanted to custom the primary key value for my Staff Info database. SQL Server will auto generate a StaffID (PK) which starts from 1, 2, 3..... and so on.

But I don't want that, can I set it to generate the StaffID (PK) such as ST20001 or ST01 instead of just 1,2,3?

Upvotes: 0

Views: 229

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

I don't see why you need to store this in the table.

CREATE TABLE dbo.Staff
(
  StaffID int IDENTITY(1,1) PRIMARY KEY,
  StaffIDForDisplay AS ('ST' + RIGHT(REPLICATE('0',9) + CONVERT(varchar(11), StaffID)))
);
GO

INSERT dbo.Staff DEFAULT VALUES;
GO 3

SELECT * FROM dbo.Staff;

Results:

StaffID    StaffIDForDisplay
=======    =================
1          ST0000000001
2          ST0000000002
3          ST0000000003

You can do similar with a view, or you could simply add/remove the ST0000... at the client when needed.

Upvotes: 2

Related Questions