Surrogate foreign key

views:

answers:

Surrogate foreign key

Oki so I have two tables: person and person_email

PERSON
------
id (PK)
person_code (Unique key 1)
person_type (Unique key 1)
surname
forename

PERSON_EMAIL
------------
id (PK)
person_id (Unique key 1) (references person(id))
email_address (Unique key 1)

Is this correct??

or should it be:

PERSON_EMAIL
------------
id (PK)
person_id (references person(id))
person_code (Unique key 1)
person_type (Unique key 1)
email_address (Unique key 1)

+2 A:

Your second suggestion includes both the unique identifiers of the PERSON table. If you try to do this, sooner or later you will find these identifiers indicate two different rows. Furthermore, changing the person_code or person_type in the PERSON table would then require a matching update of the PERSON_EMAIL table. For these reasons, I would suggest your first version is better.

Brian Hooper 2010-09-05 15:01:44

thanks for replying =]. I'm just confused about the requirement of having a natural key on every table.. I guess having person_id and email_address is valid as long as person_id points to the person_code/person_type natural key on the person table.

bob 2010-09-05 15:10:30

@bob - natural keys uniquely identify the data in a record and that's why we need to enforce them. However it is a fact that sometimes the values of natural keys change (without invalidating their identification of the record) So it is conveneient to add a surrogate (or synthetic) primary key. This uniquely identifies the row in the table but has no meaning of itself. This is the key we should reference in foreign key constraints.

APC 2010-09-05 16:52:35

ansaurus

tags:

views:

answers:

Surrogate foreign key

related questions