char(5) PK - auto increment

[thesti]

hello,

is it possible to have a char(5) primary key in a table, for example 'BR001' then will have it automatically increment by 1, so PK for the next record will be 'BR002' and so on.


Thank you.

[code green]

I don't think so because auto-increment is integer.
But you could still use an auto-increment as PK and use concatenation and padding to format the PK whenever selecting.
Or increment the char PK yourself.

[ck9663]

SQL Server does not really have an auto-incrementing field that ensures sequence. IDENTITY is an auto-incrementing column that assures uniqueness but does not assure full sequence. If you delete a row in a table, the value of the IDENTITY column will not be reused.

If you don't mind some gaps in your sequence, use an IDENTITY column to have your unique number and use a COMPUTED column to store whatever other pre/post-fix string you need attached to the counter.

However, if you need it to be fully sequential, you have to create a code to enforce the sequence. I would suggest a transaction processing inside a trigger.

Happy coding!

-- CK