Can a calculated column be used as part of a primary key?

Question

I have a table defined as:

OrderID bigint NOT NULL, IDA varchar(50) NULL, IDB bigint NULL, [ ... 50 other non relevant columns ...] 

The natural primary key for this table will be (OrderID, IDA, IDB), but this is not possible, because IDA and IDB can be null (they can both be null, but both of them are not defined at the same time), Now I have a unique restriction for these three columns.

Now I need a primary key to enable transactional replication, and I came across two options:

• Create an identity column and use it as a primary key
• Create a non-zero computed column C containing either IDA or IDB, or '' if both columns are null and use (OrderID, C) as my primary key.

The second alternative seam cleaner, since my PC will be meaningful and possible (see msdn link ), but since I never saw it done anywhere, I was wondering if they were some of the disadvantages of this approach.

Answer 1

Columns that may be null do not qualify as part of pk, since pk must also be unique.

Also PK should never be meaningful, because the meaning of a value can change.

Do tables A and B correspond? Look at the relational data model. Perhaps there may be a mistake in the design.

OrderID must be unique and therefore sufficient for the PC.