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

Question

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

The above ddl produces an 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 solve it? I want PONumber to be auto-incremented.

Answer 1

If SupplierQuoteId and PONumber are generated when a row is inserted, then the two "identity" columns would be assigned in lockstep (3504 goes with 9553, 3506 goes with 9573, 3508 goes with 9593, etc.). If this assumption is true, then you presumably could make PONumber a calculated column, like so:

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

I made supplierQuoteId NOT NULL, which ensures that PONumber will also be NOT NULL. Similarly, you no longer need the unique constraint on PONumber, as it will always be unique. (It is possible to build indexes on calculated columns, if you need one for performance.)

Answer 2

You can't have more than one identity column per table. I think your best bet would be to pull the PO data into a separate table, then relate the two with a FK column.

SupplierQuote
-------------
supplierQuoteID (PK/identity)
purchaseOrderID (FK to PurchaseOrder.purchaseOrderID)
otherColumn1

PurchaseOrder
-------------
purchaseOrderID (PK/identity)
otherColumn1