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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
Only 1 Identity per table....you should probably have a different table named PO with the PO number as the identity column.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rajkumar GsSoftware 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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Rajkumar GsSoftware 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
chapmandewCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.