5

I want to know if I can create an Identity (auto increment on a Varchar column. and how can I make it a primary key and create foreign key references on other table.

This is the code i have - 

CREATE TABLE Questions(
    QuestionID int IDENTITY PRIMARY KEY, 
    QuestionNo as 'Q'+Cast(QuestionID as Varchar(10),       
    Question Varchar(200)
)

Is there a way I can make QuestionNo as Primary key and reference it in another table (say Answers (AnswerID, QuestionNo, AnswerText)?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Abey
  • 155
  • 1
  • 2
  • 9
  • 7
    **this is a bad design, don't do it!** you can always display (in the application) the numeric value with a "Q" prefix but store it as a pure int, and FK to the int. If you need to, you can FK to two clumns, the int and a char(1) type. there is no need to store it as a varchar. I fight with old code that has this same crazy concept in it, makes everything a hack, you'll eventully have code like `IF LEFT(...,1)='Q'... ELSE IF LEFT(...,1)='A'...` or similar `CASE` statements... – KM. Sep 03 '10 at 19:35
  • Yes.I get your point. Thanks. – Abey Sep 03 '10 at 19:48
  • @KM: Agreed. I had one system that had a setup similar to this - they wanted the numbers to have a couple of letters leading into the record number... It got better when they started using the system, and wanted the search updated so it *ignored the leading letters*... – OMG Ponies Sep 03 '10 at 19:55

3 Answers3

9

This worked for me on SQL Server 2005:

CREATE TABLE Questions(
  QuestionID int IDENTITY NOT NULL, 
  QuestionNo as 'Q'+Cast(QuestionID as Varchar(10)) PERSISTED PRIMARY KEY, 
  Question Varchar(200)
)

The main part is that a computed column needs the PERSISTED keyword...

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • But how would you refer this in another table as a foreign key? I get Error from this CREATE TABLE Answers(AnswerID int IDENTITY PRIMARY KEY, QuestionNo Varchar(10), Answer nvarchar(200), Foreign key (QuestionNo) references Questions(QuestionNo)) – Abey Sep 03 '10 at 19:29
  • @Abey: You get an error because the length of the `questionno` is VARCHAR(10) in the `ANSWERS` table, but it's VARCHAR(11) in `QUESTIONS` - once that's corrected, it works for me. But I agree with `gbn` - I wouldn't make `questionno` the primary key both for speed & likelihood of format change in the future. – OMG Ponies Sep 03 '10 at 19:40
3

Not directly.

  • use a computed column on an integer column (as per OMG Ponies answer)
  • use a udf (SO1, SO2)

My question is: why? it will be slower than a straightforward number.

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Just wanted the identity key to be more descriptive like Q1, Q2 rather than plan numbers – Abey Sep 03 '10 at 19:31
-1

this code is working

CREATE TABLE IdentityExample(
  ID int IDENTITY NOT NULL, 
  QNo as 'Q'+Cast(ID as Varchar(10)) PERSISTED PRIMARY KEY, 
  name Varchar(200)
)
Ashokreddy
  • 352
  • 1
  • 11