sqlsql-servert-sqlsurrogate-key

Formatting the surrogate key based on other columns


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

Solution

  • Just use a computed column:

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