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

You can't solve you - you can only have a single IDENTITY column per table. No way around that, sorry.

The only "hackish" solution would be to have a separate table for nothing more than having an INT IDENTITY field, and grabbing the newest value from that helper table into your entity upon insertion (e.g. with a trigger). Not very pretty, but it might work for you.

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

Answer 3

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 4

I think I'd use a trigger to fill the "second identity".

Answer 5

If there is only one PO id per supplier quote, then why not simply use the supplier quote id as the PO id?

If there can be more than one, you must have a sepapate table with a foreign key constraint. You can of course use cascade delete to delete from this table but this can be dangerous if you delete too many records (causing lockups) or personally I wouldn't want to delete a supplier quote if a PO number has been created as that means the item quoted was actually bought. You do not want to ever destroy records of things that were actually purchased. Since you will likely have multiple POS (I got a quote on six things and first bought three of them, then bought two others the next week) per quote and since it is likely you will want to store specific information about the purchase order, I recommend a separate table. To do anything else is going to cause you problems in the long run.