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?