What is the correct way of creating a one-to-one relationship?

2 min read Original article ↗

The most important thing is to word constraints precisely and not allow for logical errors. Options 1, 2, 3 contain logical errors. Option 4 is close, but may not represent reality very good -- in general one person may have more than one passport (dual citizenship).

Option 1 is not realistic, because it states that there can not exist a person without a passport.

Option 2 essentially allows a passport to exist without a person..

Option 3 states that passport is a person.

Option 4 is the closest and can be worded as:

[P1] Person (PERSON_ID) named (NAME) exists.

(c1.1) Person is identified by PERSON_ID.

(c1.2) Each person has exactly one name; for each name, more than one person can have that name.

[P2] Passport (PASSPORT_ID) issued by country (COUNTRY) is owned by person (PERSON_ID)

(c2.1) Passport is Identified by PASSPORT_ID.

(c2.2) Each passport is issued by exactly one country; for each country, more than one passport can be issued by that country.

(c2.3) Each passport is owned by exactly one person; for each person that person may own at most one passport.

(c2.4) If a passport issued by a country is owned by a person then that person must exist.

person {PERSON_ID, NAME}  -- p1
   KEY {PERSON_ID}        -- c1.1



passport {PASSPORT_ID, COUNTRY, PERSON_ID}  -- p2
     KEY {PASSPORT_ID}                      -- c2.1
     KEY {PERSON_ID}                        -- c2.3

FOREIGN KEY {PERSON_ID} REFERENCES person {PERSON_ID} -- c2.4

Note:

[Px]   = predicate x
[cx.y] = constraint x.y

KEY = PK or AK

PK  = PRIMARY KEY
AKn = ALTERNATE KEY (UNIQUE)
FKn = FOREIGN KEY

All attributes NOT NULL

EDIT

Just to be clear, option 3 would have been the correct choice had you chosen a different example. Say, Person & Employee or Employee & Accountant instead of Person & Passport. Because an employee is a person, and an accountant is an employee. The is-a relationship implies a proper subset.

This is a great example of how focusing on technical details may introduce logical errors, and there is no such a thing as a small logical error.