Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
OrderNumber
OrderDate
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?

share|improve this question
    
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

1 Answer 1

up vote 5 down vote accepted

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

CREATE TABLE foo
(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

share|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
    
Cool stuff, thank you. –  md1337 Apr 26 '13 at 17:01
    
@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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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