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?