Advertisement

Formatting the surrogate key based on other columns

sql sql-server surrogate-key tsql

I have a table in SQL Server

create table student
(
    id int,
    batch varchar(10),
    branch varchar(10),
    registerNumber varchar(30)
)

I want my registerNumber to be of type batch+branch+id
eg a row in student id:1, batch:17, branch:BIT then the registerNumber should be 17BIT1

I tried to do this by using a default value in create table but it does not allow me to reference the columns of the same table

edit

thank you for the answers I went with this

create table stud101
(
batch int,
branch char(3),
srNum int identity(1,1),
registerNum as Concat(cast(batch as char(2)),branch,cast(format(srNum,'0000') as char(4))
))

insert into stud101 values(17,'BIT')


select * from stud101

Advertisement

Answer

Just use a computed column:

create table student
(
    id int,
    batch varchar(10),
    branch varchar(10),
    registerNumber as batch + branch + id
)

Advertisement