One-to-one relationship design in SQL - Step-by-Step Execution

Concept Flow - One-to-one relationship design

Create Table A

↓

Create Table B

↓

Add Primary Key to A

↓

Add Primary Key to B

↓

Add Foreign Key in B referencing A

↓

Enforce Unique Constraint on Foreign Key in B

↓

One-to-One Relationship Established

Create two tables each with a primary key. Then add a foreign key in one table referencing the other, with a unique constraint to ensure one-to-one mapping.

Execution Sample

SQL

CREATE TABLE Person (
  PersonID INT PRIMARY KEY,
  Name VARCHAR(100)
);

CREATE TABLE Passport (
  PassportID INT PRIMARY KEY,
  PersonID INT UNIQUE,
  FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
);

Creates two tables Person and Passport with a one-to-one relationship via PersonID.

Execution Table

Step	Action	Result	Notes
1	Create Person table with PersonID primary key	Person table created	PersonID uniquely identifies each person
2	Create Passport table with PassportID primary key	Passport table created	PassportID uniquely identifies each passport
3	Add PersonID column to Passport with UNIQUE constraint	PersonID column added	Ensures one passport per person
4	Add FOREIGN KEY constraint on Passport.PersonID referencing Person.PersonID	Foreign key constraint added	Links passport to person
5	Insert PersonID=1, Name='Alice' into Person	Row inserted	Person Alice added
6	Insert PassportID=101, PersonID=1 into Passport	Row inserted	Passport linked to Alice
7	Attempt to insert PassportID=102, PersonID=1 into Passport	Error: UNIQUE constraint violation	Cannot assign second passport to same person
8	Attempt to insert PassportID=103, PersonID=2 into Passport	Error: FOREIGN KEY violation	PersonID=2 does not exist in Person table
9	Insert PersonID=2, Name='Bob' into Person	Row inserted	Person Bob added
10	Insert PassportID=103, PersonID=2 into Passport	Row inserted	Passport linked to Bob

💡 Execution stops after successful inserts and constraint violations prevent invalid data.

Variable Tracker

Table	Start	After Step 5	After Step 6	After Step 9	After Step 10
Person	empty	[{PersonID:1, Name:'Alice'}]	[{PersonID:1, Name:'Alice'}]	[{PersonID:1, Name:'Alice'}, {PersonID:2, Name:'Bob'}]	[{PersonID:1, Name:'Alice'}, {PersonID:2, Name:'Bob'}]
Passport	empty	empty	[{PassportID:101, PersonID:1}]	[{PassportID:101, PersonID:1}]	[{PassportID:101, PersonID:1}, {PassportID:103, PersonID:2}]

Key Moments - 3 Insights

Why does inserting a second passport with the same PersonID fail?

Because the UNIQUE constraint on Passport.PersonID prevents multiple passports linking to the same person, ensuring one-to-one relationship (see Step 7 in execution_table).

Why can't we insert a passport with a PersonID that doesn't exist in Person?

The FOREIGN KEY constraint requires the PersonID in Passport to exist in Person table, so inserting a non-existent PersonID causes an error (see Step 8).

Why do we add UNIQUE constraint on the foreign key column?

To ensure each PersonID appears only once in Passport, enforcing one-to-one instead of one-to-many relationship.

Visual Quiz - 3 Questions

Look at the execution_table, what happens at Step 7 when inserting PassportID=102 with PersonID=1?

A Row inserted successfully

B Error due to UNIQUE constraint violation

C Error due to FOREIGN KEY violation

D No action taken

💡 Hint

Check Step 7 in execution_table where the UNIQUE constraint on PersonID causes the error.

At which step does the Person table first get a row inserted?

A Step 1

B Step 3

C Step 5

D Step 6

💡 Hint

Look at execution_table rows for when PersonID=1 is inserted.

If we remove the UNIQUE constraint on Passport.PersonID, what would happen?

A Multiple passports can link to the same person

B Foreign key constraint will fail

C Person table will reject inserts

D No change in behavior

💡 Hint

Refer to key_moments explaining the role of UNIQUE constraint in enforcing one-to-one.

Concept Snapshot

One-to-one relationship design:
- Create two tables each with a primary key.
- Add a foreign key in one table referencing the other.
- Add UNIQUE constraint on the foreign key column.
- This ensures each row in one table matches exactly one row in the other.
- Prevents duplicates and enforces strict pairing.

Full Transcript

This visual execution trace shows how to design a one-to-one relationship in SQL. First, we create two tables Person and Passport, each with a primary key. Then, we add a PersonID column to Passport with a UNIQUE constraint and a foreign key referencing Person.PersonID. This setup ensures each passport links to exactly one person and vice versa. The execution table walks through creating tables, adding constraints, inserting valid rows, and shows errors when constraints are violated. The variable tracker shows how data changes in both tables after each step. Key moments clarify why UNIQUE and foreign key constraints are needed. The quiz tests understanding of constraint enforcement and insertion order. The snapshot summarizes the key steps to enforce one-to-one relationships in databases.