I'm designing a table to be used by a Web App, and my framework requires that the primary key be a surrogate key. But I also need an extra field which will be a composite of 2 other fields. In other words:

Id -> Primary key
UniqueOrderNumber -> a composite of OrderNumber and OrderDate

UniqueOrderNumber should be a string generated by merging OrderNumber and OrderDate. This new field will then by used in certain queries that require a unique OrderNumber.

I wanted to avoid handling this logic in the application as I feel this should be a job for the databate. Is there a way to have the database generate this UniqueOrderNumber field automatically by concatenating the values of OrderNumber and OrderDate?

  • I'm building a DB against a legacy system and OrderNumbers are NOT unique and they roll over, so I will make them unique in my DB using the dates. I need them unique because of some queries that require the OrderNumbers to be unique. And I also NEED a surrogate key because of the ORM framework I use (this is Id). – md1337 Apr 26 '13 at 16:53
  • 2
    Comments Removed -- Keep the comments civil, please. Md1337 you are here looking for help, please don't take criticism of your design personally. – JNK Apr 26 '13 at 17:58

You could leverage computed columns for this. So your create table would look like:

(ID int not null,
 OrderNumber int not null,
 OrderDate datetime not null,
 UniqueOrderNumber AS 
   (CONVERT(varchar(20),OrderNumber) + ":" + CONVERT(varchar(20),OrderDate,112))

SQL Fiddle Example

| improve this answer | |
  • This works well for us. Our requirements are very different than yours but we effectively do this to calculate a datetime column from a few other columns (month number, day number, year number, etc). In our case, the datetime column is only there to assist with queries but are ultimately unimportant. – Jaxidian Apr 26 '13 at 16:07
  • @md1337 ... and if the data doesn't change (which i'd hope it wouldnt if it seems like you'd originally thought it would be a good PK), you can persist the computed column – swasheck Apr 27 '13 at 23:07
  • Just wondering if UniqueOrderNumber is really unique can we enforce a unique constraint on it? – StixO Dec 26 '19 at 4:48

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.