How to reference a composite primary key into a single field?

Question

I got this composite primary key in Table 1:

Table 1: Applicant

CreationDate PK
FamilyId PK
MemberId PK 

I need to create a foreign key in Table 2 to reference this composite key. But i do not want to create three fields in Table 2 but to concatenate them in a single field.

Table 2: Sales

SalesId int, 
ApplicantId  -- This should be "CreationDate-FamilyId-MemberId"

What are the possible ways to achieve this ?

Note: I know i can create another field in Table 1 with the three columns concatenation but then i will have redundant info

Answer 1

What you're asking for is tantamount to saying "I want to treat three pieces of information as one piece of information without explicitly making it one piece of information". Which is to say that it's not possible.

That said, there are ways to make happen what you want to happen

• Create a surrogate key (i.e. identity column) and use that as the FK reference
• Create a computed column that is the concatenation of the three columns and use that as the FK reference

All else being equal (ease of implementation, politics, etc), I'd prefer the first. What you have is really a natural key and doesn't make a good PK if it's going to be referenced externally. Which isn't to say that you can't enforce uniqueness with a unique key; you can and should.