Loading learning content...
The foreign key approach is the most common and versatile strategy for mapping 1:1 relationships to relational schemas. It maintains the conceptual separation of both entity types by representing each as its own table, then establishes the relationship through a foreign key reference combined with a uniqueness constraint.
This approach preserves the ER model's structure most directly—two entities become two tables, and the relationship becomes a referential constraint. However, the apparent simplicity conceals important decisions: Which table should contain the foreign key? The answer significantly impacts data integrity, NULL space consumption, query patterns, and schema maintainability.
By the end of this page, you will master the foreign key approach to 1:1 mapping, understand the principles governing foreign key placement, implement uniqueness constraints that enforce 1:1 cardinality, and recognize how participation constraints guide design decisions.
In the foreign key approach, we create two relational tables (one for each entity type) and place a foreign key column in one table that references the primary key of the other. For this to correctly represent a 1:1 relationship rather than a 1:N relationship, we must add a UNIQUE constraint on the foreign key column.
Why UNIQUE is essential:
A foreign key alone ensures that every value in the referencing column exists in the referenced table—this enforces referential integrity. But without uniqueness, multiple rows in the referencing table could point to the same row in the referenced table, producing a 1:N relationship.
The UNIQUE constraint prevents this by ensuring no two rows in the referencing table can have the same foreign key value. Combined, these constraints guarantee that each entity instance on the primary key side associates with at most one entity on the foreign key side.
123456789101112131415161718192021222324252627
-- Entity 1: Employee (primary entity in this example)CREATE TABLE employee ( employee_id INT PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, hire_date DATE NOT NULL); -- Entity 2: Personnel File (references Employee)CREATE TABLE personnel_file ( file_id INT PRIMARY KEY, employee_id INT UNIQUE NOT NULL, -- UNIQUE enforces 1:1 salary DECIMAL(12, 2), performance VARCHAR(20), bonus_history TEXT, -- Foreign key establishes relationship CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES employee(employee_id) ON DELETE CASCADE); -- The combination of FOREIGN KEY + UNIQUE on employee_id means:-- 1. Every personnel file must reference a valid employee (referential integrity)-- 2. No two personnel files can reference the same employee (1:1 cardinality)For 1:1 relationships via foreign key:
FOREIGN KEY → Ensures valid references (referential integrity) UNIQUE → Ensures single references (cardinality constraint)
Both constraints are essential. Omitting UNIQUE produces 1:N; omitting FOREIGN KEY produces unchecked references.
Alternative: Using Primary Key as Foreign Key
An elegant variation uses the foreign key column as the primary key of the referencing table. Since primary keys are inherently unique, this eliminates the need for a separate UNIQUE constraint while serving the same purpose:
1234567891011121314151617181920212223
-- Personnel File uses employee_id as its own primary keyCREATE TABLE personnel_file ( employee_id INT PRIMARY KEY, -- Same value as referenced employee salary DECIMAL(12, 2), performance VARCHAR(20), bonus_history TEXT, CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES employee(employee_id) ON DELETE CASCADE); -- Benefits of this approach:-- 1. No separate surrogate key needed (space efficiency)-- 2. Primary key inherently enforces uniqueness-- 3. Joins use same column name on both sides-- 4. Implicit documentation: same PK value = same entity -- Query joining both tables:SELECT e.first_name, e.last_name, pf.salaryFROM employee eJOIN personnel_file pf ON e.employee_id = pf.employee_id;In 1:1 relationships, a structural symmetry exists: the foreign key could theoretically reside in either table. Both configurations would satisfy the cardinality constraint. However, not all placements are equal—the optimal placement depends on participation constraints, and choosing incorrectly introduces NULL values, complicates constraints, or violates data integrity.
The Fundamental Principle:
Place the foreign key in the table representing the entity with TOTAL (mandatory) participation.
This principle emerges from NULL semantics. A NULL foreign key represents "no associated entity," which corresponds to non-participation in the relationship. If an entity has total participation, it must participate—NULLs would violate this requirement. Therefore, the foreign key belongs where NULLs should never occur.
| Participation Pattern | E₁ Constraint | E₂ Constraint | FK Placement | Rationale |
|---|---|---|---|---|
| Total-Total (1,1):(1,1) | Mandatory | Mandatory | Either table works | Both entities always participate; no NULLs either way |
| Total-Partial (1,1):(0,1) | Mandatory | Optional | In E₁'s table | E₁ always has E₂; E₂ may lack E₁ → FK in E₁ avoids NULLs |
| Partial-Total (0,1):(1,1) | Optional | Mandatory | In E₂'s table | E₂ always has E₁; E₁ may lack E₂ → FK in E₂ avoids NULLs |
| Partial-Partial (0,1):(0,1) | Optional | Optional | Either, or use cross-reference table | NULLs unavoidable with two tables; third table eliminates them |
If you place the foreign key in the table with partial participation, you will have NULL foreign key values for every non-participating entity. This wastes storage, complicates queries (requiring IS NOT NULL checks), and violates best practices that discourage NULLable foreign keys.
Let's examine concrete implementations for each participation pattern, demonstrating correct foreign key placement and constraint specification.
Scenario: PASSPORT ↔ CITIZEN
1234567891011121314151617181920212223242526
-- CITIZEN table (partial participation)CREATE TABLE citizen ( citizen_id INT PRIMARY KEY, full_name VARCHAR(100) NOT NULL, date_of_birth DATE NOT NULL, national_id VARCHAR(20) UNIQUE NOT NULL); -- PASSPORT table (total participation - contains foreign key)CREATE TABLE passport ( passport_number VARCHAR(20) PRIMARY KEY, citizen_id INT UNIQUE NOT NULL, -- UNIQUE + NOT NULL issue_date DATE NOT NULL, expiry_date DATE NOT NULL, issuing_office VARCHAR(100), CONSTRAINT fk_citizen FOREIGN KEY (citizen_id) REFERENCES citizen(citizen_id) ON DELETE CASCADE -- If citizen deleted, passport deleted); -- Query: Find all citizens and their passports (if any)SELECT c.full_name, p.passport_number, p.expiry_dateFROM citizen cLEFT JOIN passport p ON c.citizen_id = p.citizen_id;Foreign key constraints can specify referential actions that determine what happens when a referenced row is updated or deleted. For 1:1 relationships, these actions have specific implications based on the semantic coupling between entities.
| Action | ON DELETE | ON UPDATE | 1:1 Use Case |
|---|---|---|---|
| CASCADE | Delete dependent row | Update FK value | Strongly coupled entities (employee & personnel file) |
| SET NULL | Set FK to NULL | Set FK to NULL | Independent entities with optional relationship |
| SET DEFAULT | Set FK to default | Set FK to default | Rare in 1:1; may indicate design issue |
| RESTRICT | Prevent deletion | Prevent update | Enforce deletion order; delete dependent first |
| NO ACTION | Check after statement | Check after statement | SQL standard default; deferred constraint checking |
For 1:1 relationships with total-total participation, CASCADE is usually appropriate—the entities' lifecycles are tightly coupled. For partial participation on the FK side, SET NULL allows the referenced entity to exist independently. RESTRICT is useful when you want explicit control over deletion order.
1234567891011121314151617181920212223242526272829303132
-- CASCADE: Personnel file deleted with employeeCREATE TABLE personnel_file ( file_id INT PRIMARY KEY, employee_id INT UNIQUE NOT NULL, -- ... CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES employee(employee_id) ON DELETE CASCADE -- Delete file when employee deleted ON UPDATE CASCADE -- Update FK if employee PK changes); -- SET NULL: Parking space becomes unassignedCREATE TABLE parking_space ( space_id INT PRIMARY KEY, employee_id INT UNIQUE, -- ... CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES employee(employee_id) ON DELETE SET NULL -- Space becomes unassigned ON UPDATE CASCADE -- Track employee ID changes); -- RESTRICT: Prevent deletion until dependent removedCREATE TABLE driver_license ( license_id INT PRIMARY KEY, person_id INT UNIQUE NOT NULL, -- ... CONSTRAINT fk_person FOREIGN KEY (person_id) REFERENCES person(person_id) ON DELETE RESTRICT -- Must delete license first ON UPDATE CASCADE);The foreign key approach influences query construction. Understanding common query patterns helps evaluate whether this mapping strategy suits your access requirements.
12345678910111213141516171819202122232425262728293031323334353637
-- Setup: EMPLOYEE ↔ PERSONNEL_FILE (FK in personnel_file) -- Pattern 1: Retrieve combined entity data (INNER JOIN)-- Returns only employees who have personnel filesSELECT e.employee_id, e.name, pf.salary, pf.performanceFROM employee eINNER JOIN personnel_file pf ON e.employee_id = pf.employee_id; -- Pattern 2: Retrieve primary with optional related (LEFT JOIN)-- Returns all employees, with NULL for those without filesSELECT e.employee_id, e.name, COALESCE(pf.salary, 0) AS salaryFROM employee eLEFT JOIN personnel_file pf ON e.employee_id = pf.employee_id; -- Pattern 3: Find unrelated entities (LEFT JOIN with NULL check)-- Returns employees who don't have personnel filesSELECT e.employee_id, e.nameFROM employee eLEFT JOIN personnel_file pf ON e.employee_id = pf.employee_idWHERE pf.employee_id IS NULL; -- Pattern 4: Existence check (subquery)-- Check if employee has personnel file without fetching itSELECT e.employee_id, e.name, EXISTS (SELECT 1 FROM personnel_file pf WHERE pf.employee_id = e.employee_id) AS has_fileFROM employee e; -- Pattern 5: Related data update-- Update through join (syntax varies by DBMS)UPDATE personnel_file pfSET pf.salary = pf.salary * 1.1WHERE pf.employee_id IN ( SELECT e.employee_id FROM employee e WHERE e.department = 'Engineering');Since 1:1 joins produce at most one matching row per driving table row, query optimization is straightforward. Ensure indexes exist on both the primary key and the foreign key column. Most databases automatically index primary keys; you may need to explicitly create an index on the foreign key if it's not already covered by the UNIQUE constraint's implicit index.
Use the foreign key approach when:
• At least one side has partial participation (to avoid NULLs) • Entities have distinct access patterns or security requirements • Tables are queried independently as often as together • The entity types may evolve independently over time • You need to maintain clear conceptual separation
The foreign key approach is the most versatile strategy for mapping 1:1 relationships, but applying it effectively requires understanding the interplay between participation constraints and foreign key placement.
What's Next:
When both entities have total participation and are semantically tightly coupled, a different approach may be more appropriate: merging both entities into a single table. The next page explores this merged table strategy, examining when consolidation is preferable to maintaining separate tables.
You now understand how to implement 1:1 relationships using the foreign key approach, including constraint specification, placement decisions, and referential actions. This knowledge enables you to maintain entity separation while ensuring relational integrity.