CODEWITHSUNDEEP
sql-server
gaurav bali
gaurav bali

Reputation: 63

Autoincrement of primary key column with varchar datatype in it

CREATE TABLE Persons
(
    P_Id varchar(6) NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    PRIMARY KEY (P_Id)
)

in this table P_Id is the primary key. We want to generate autoincrement of P_Id with default value (PN00) in the start while inserting only LastName and FirstName .eg :-PN001 for first entry ,PN002 for second,PN003 for third and so on .

Upvotes: 3

Views: 4590

Answers (2)

Constantine
Constantine

Reputation: 1406

There are different ways to address your issue.

  1. You could use a Trigger.Triggers are activated on some events. You could create a trigger for 'Instead of Insert On Persons' event. When the event is triggered, then generate a new P_Id. Insert this new P_Id alongwith all the values as the new record for your table.

This approach wont have coupling with the table's schema.

Refer this link for more information on Triggers : https://msdn.microsoft.com/en-IN/library/ms189799.aspx

Refer this link to emulate 'before insert trigger' in SQL Server: How to emulate a BEFORE INSERT trigger in T-SQL / SQL Server for super/subtype (Inheritence) entities?

  1. You could also use a Procedure like :

    create procedure Persons_insert(@lastname varchar(255), @firstname                                    varchar(255))
as 
begin
 --write code to generate the ID as you like
 insert into Persons(p_id,lastname,firstname)values(generated_id,lastname,firstname);
end

Upvotes: 0

marc_s
marc_s

Reputation: 754388

The only viable solution is to use

  • an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value
  • a computed, persisted column to convert that numeric value to the value you need

So try this:

CREATE TABLE dbo.Persons
  (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   P_ID AS 'PN' + RIGHT('00000' + CAST(ID AS VARCHAR(5)), 5) PERSISTED,
   .... your other columns here....
  )

Now, every time you insert a row into Persons without specifying values for ID or P_ID:

INSERT INTO dbo.Persons(Col1, Col2, ..., ColN)
VALUES (Val1, Val2, ....., ValN)

then SQL Server will automatically and safely increase your ID value, and P_Id will contain values like PN00001, PN00002,...... and so on - automatically, safely, reliably, no duplicates.

Upvotes: 8

Related Questions