How to insert values in table with auto increment primary key?

Question

I want to create a table which has multiple columns with primary key roll no. And this roll no. will be like cse001, cs002, cs003 and so on.

And other columns will be name, gender, branch etc.

So if I add a new row it should be added with new roll no. (Cs***) incremented by one.

So how should I create my table ?

I'm using SQL Server 2008.

i don't know sql server 2008 but multiple primary keys sounds wrong and i would be surprised if thats possible. There might be a reason why it is called 'primary'... ;) — Markus Kottländer
– Markus Kottländer, Commented Dec 4, 2013 at 20:14
Markus, if I understand correctly he's talking about having one primary key column, but it's alphanumeric and incremented as if it were a numeric column. — LNendza
– LNendza, Commented Dec 4, 2013 at 20:16
The best use of a primary key is an auto field that you let the database take care of for you. It should not contain any relevant information that you would need. Instead it should be used strictly to link tables together via primary key - foreign key relationships. if you need CS001, CS002, and etc data format, that should be in a separate field and not be the primary key. DISCLAIMER: Just my two cents — Linger
– Linger, Commented Dec 4, 2013 at 20:17
I would probably write some sort of trigger that would take the row count of the table, create a concatenated string of CS and the number, and then insert a new record with that as the key. Still, I don't understand why you'd want to do this over a regular, numeric primary key. — LNendza
– LNendza, Commented Dec 4, 2013 at 20:18
I think if he really need to do that, soon he need to put on the table a trigger "after insert" filling this field "CS000" merging "CS" with primary key. — Andrew Paes
– Andrew Paes, Commented Dec 4, 2013 at 20:21

Phil Sandler · Accepted Answer · 2013-12-04 20:40:15Z

Edit: SQL Fiddle

You can use an IDENTITY column as a non-PK field, and then use a calculated column for your Primary Key:

CREATE TABLE TestTable ( PKOfTable AS ('CS' + CAST(AutoIncId as varchar)) PERSISTED NOT NULL PRIMARY KEY, [AutoIncId] INT IDENTITY NOT NULL, [OtherField] VARCHAR(10) ); INSERT INTO TestTable (OtherField) VALUES ('hello1') INSERT INTO TestTable (OtherField) VALUES ('hello2') INSERT INTO TestTable (OtherField) VALUES ('hello3') INSERT INTO TestTable (OtherField) VALUES ('hello4') SELECT * FROM TestTable

It can be use another field in the table as well. SQL Fiddle appears to be down, but e.g. PKOfTable AS (OtherField + CAST(AutoIncId as varchar)) PERSISTED NOT NULL PRIMARY KEY.

Collectives™ on Stack Overflow

How to insert values in table with auto increment primary key?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related