I have a db with a table that has a rollover value such as OrderNumber and another column is year.

This is the primary key - a composite key - but there are other tables with which I need to use a foreign key relationship - I really would prefer to have an auto increment surrogate key for this table, the trick though is that OrderNumber and Year must be unique. IS there a good way to handle this?

I was reading here for some inspirations. Composite field in SQL Server, does it exist?

  • 1
    Yes. Use your surrogate key for joins with other tables, but put a UNIQUE index on (Year, OrderNumber). At least from what you've told us, that would seem to be the best solution (again, given what you've told us). – Vérace Dec 26 '19 at 6:31
  • @Vérace are you saying create the Computed Column Year_OrderNumber, as unique column then simply add the auto increment surrogate key as primary key? Because Order number cycles 1 through 9999 and each year it rolls over. so in 1996 there is an order 150 and also in 1997 an order 150. There are multiple records with the same year, and also with the same order number. – StixO Dec 26 '19 at 15:24
  • 1
    What I've suggested should work under this scenario, hence the year field the unique index. – Vérace Dec 26 '19 at 21:05

As Vérace suggested, you can do both: create a surrogate (auto-increment) key to simplify joins and related table structures, and add a unique constraint on the two current PK columns to continue to enforce the proper data integrity.

In SQL Server, multiple columns can participate in a unique index / constraint:

ALTER TABLE dbo.Orders
UNIQUE ([Year], OrderNumber);

Thus there is no need to create a computed column to combine the two columns.

  • I have never seen this syntax Unique ([Year], OrderNumber) before - this makes the uniqueness based on these two fields - the two not really being treated independently but more like one field - as far as uniqueness is concerned? – StixO Dec 28 '19 at 19:57
  • 1
    @StixO yep, just like that. It means only unique combinations of those two columns can exist in the table. – Josh Darnell Dec 29 '19 at 1:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.