How to autoincrement a varchar

Question

Can I make a primary key like 'c0001, c0002' and for supplier 's0001, s0002' in one table?

Answer 1

1. The idea in database design, is to keep each data element separate. And each element has its own datatype, constraints and rules. That c0002 is not one field, but two. Same with XXXnnn or whatever. It is incorrect , and it will severely limit your ability to use the data, and use database features and facilities.

   Break it up into two discrete data items:
   column_1 CHAR(1)
column_2 INTEGER

   Then set AUTOINCREMENT on column_2

   And yes, your Primary Key can be (column_1, column_2), so you have not lost whatever meaning c0002 has for you.

2. Never place suppliers and customers (whatever "c" and "s" means) in the same table. If you do that, you will not have a database table, you will have a flat file. And various problems and limitations consequent to that.

   That means, Normalise the data. You will end up with:

   • one table for Person or Organisation containing the common data (Name, Address...)
   • one table for Customer containing customer-specific data (CreditLimit...)
   • one table for Supplier containing supplier-specific data (PaymentTerms...)
   • no ambiguous or optional columns, therefore no Nulls
   • no limitations on use or SQL functions
     .

   And when you need to add columns, you do it only where it is required, without affecting all the other sues of the flat file. The scope of effect is limited to the scope of change.

Answer 2

My approach would be:

• create an ID INT IDENTITY column and use that as your primary key (it's unique, narrow, static - perfect)

• if you really need an ID with a letter or something, create a computed column based on that ID INT IDENTITY

Try something like this:

CREATE TABLE dbo.Demo(ID INT IDENTITY PRIMARY KEY,
                      IDwithChar AS 'C' + RIGHT('000000' + CAST(ID AS VARCHAR(10)), 6) PERSISTED
                     )

This table would contain ID values from 1, 2, 3, 4........ and the IDwithChar would be something like C000001, C000002, ....., C000042 and so forth.

With this, you have the best of both worlds:

• a proper, perfectly suited primary key (and clustering key) on your table, ideally suited to be referenced from other tables

• your character-based ID, properly defined, computed, always up to date.....

Answer 3

Yes, Actually these are two different questions, 1. Can we use varchar column as an auto increment column with unique values like roll numbers in a class

ANS: Yes, You can get it right by using below piece of code without specifying the value of ID and P_ID,

CREATE TABLE dbo.TestDemo
  (ID INT IDENTITY(786,1) NOT NULL PRIMARY KEY CLUSTERED,
   P_ID AS 'LFQ' + RIGHT('00000' + CAST(ID AS VARCHAR(5)), 5) PERSISTED,
   Name varchar(50),
   PhoneNumber varchar(50)
  )

2. Two different increments in the same column,

ANS: No, you can't use this in one table.