How to Auto-Increment Non-Primary Key? - MS SQL Server

CREATE TABLE SupplierQuote
(
      supplierQuoteID   int identity (3504,2) CONSTRAINT supquoteid_pk PRIMARY KEY,
      PONumber            int identity (9553,20) NOT NULL
        .
        .
);

I get this error:

Msg 2744, Level 16, State 2, Line 1
Multiple identity columns specified for table 'SupplierQuote'. Only one identity column per table is allowed.

How can i fix this error?
LVL 1
F-J-KAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
Only 1 Identity per table....you should probably have a different table named PO with the PO number as the identity column.
0
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
A table cannot contain more than one IDENTITY column.

In the table, you posted, first two columns itself are IDENTITY columns. It will not work.

Remove second IDENTITY column to fix it.

Then it will become

CREATE TABLE SupplierQuote
(
      supplierQuoteID   int identity (3504,2) CONSTRAINT supquoteid_pk PRIMARY KEY,
      PONumber            int (9553,20) NOT NULL
        .
        .
);

Hope this helps
Raj
0
 
F-J-KAuthor Commented:
@raj

But  PONumber  int (9553,20) won't be auto-incremented, will it?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
Sorry, I missed it out

CREATE TABLE SupplierQuote
(
      supplierQuoteID   int identity (3504,2) CONSTRAINT supquoteid_pk PRIMARY KEY,
      PONumber            int NOT NULL
        .
        .
);

Raj
0
 
F-J-KAuthor Commented:
>>Only 1 Identity per table....you should probably have a different table named PO with the PO number as the identity column.

I think that's fastest way...

I wonder why only one column is allowed to be auto-incremented?
0
 
chapmandewConnect With a Mentor Commented:
Well, does it make sense to have 2 columsns that will essentially have the same values?  Likely not. Better design to originate your surrogate PO from a different table where it can have it own attributes...
0
 
F-J-KAuthor Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.