One-to-one relationship design in SQL - Mini Project: Build & Apply

One-to-One Relationship Design in SQL

📖 Scenario: You are designing a simple database for a company. Each employee has exactly one company car assigned to them. You need to create tables to store employee details and their assigned car details, ensuring a one-to-one relationship between employees and cars.

🎯 Goal: Create two tables, Employees and CompanyCars, with a one-to-one relationship between them using a foreign key constraint.

📋 What You'll Learn

Create a table called Employees with columns EmployeeID (primary key) and Name (text).

Create a table called CompanyCars with columns CarID (primary key), Model (text), and EmployeeID (foreign key).

Ensure the EmployeeID column in CompanyCars enforces a one-to-one relationship with Employees.

Add a unique constraint on EmployeeID in CompanyCars to prevent multiple cars assigned to the same employee.

💡 Why This Matters

🌍 Real World

One-to-one relationships are common in databases when each entity in one table corresponds to exactly one entity in another, such as employees and their assigned company cars.

💼 Career

Understanding how to design and enforce one-to-one relationships is important for database design roles, backend development, and data modeling tasks.

1

Create the Employees table

Write a SQL statement to create a table called Employees with two columns: EmployeeID as an integer primary key and Name as text.

SQL

-- Your code here to create Employees table

Hint

Use CREATE TABLE with EmployeeID as the primary key and Name as a text column.

2

Create the CompanyCars table with foreign key

Write a SQL statement to create a table called CompanyCars with columns: CarID as an integer primary key, Model as text, and EmployeeID as an integer foreign key referencing Employees(EmployeeID).

SQL

CREATE TABLE Employees (
    EmployeeID INTEGER PRIMARY KEY,
    Name TEXT
);
-- Your code here to create CompanyCars table with foreign key

Hint

Remember to add a foreign key constraint on EmployeeID referencing Employees(EmployeeID).

3

Add unique constraint to enforce one-to-one

Modify the CompanyCars table creation SQL to add a unique constraint on the EmployeeID column to ensure each employee can have only one car.

SQL

CREATE TABLE Employees (
    EmployeeID INTEGER PRIMARY KEY,
    Name TEXT
);

CREATE TABLE CompanyCars (
    CarID INTEGER PRIMARY KEY,
    Model TEXT,
    EmployeeID INTEGER,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
    -- Your code here to add unique constraint on EmployeeID
);

Hint

Add UNIQUE after EmployeeID INTEGER to enforce one-to-one relationship.

4

Complete the one-to-one relationship design

Ensure the full SQL code includes the Employees table and the CompanyCars table with EmployeeID as a unique foreign key to enforce the one-to-one relationship.

SQL

CREATE TABLE Employees (
    EmployeeID INTEGER PRIMARY KEY,
    Name TEXT
);

CREATE TABLE CompanyCars (
    CarID INTEGER PRIMARY KEY,
    Model TEXT,
    EmployeeID INTEGER UNIQUE,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);
-- Review and finalize your SQL code

Hint

Check that both tables are created and the unique foreign key constraint is present.

Practice

1. What is a key characteristic of a one-to-one relationship in database design?

easy

A. Each row in one table matches exactly one row in another table

B. Each row in one table can match many rows in another table

C. Rows in both tables have no connection

D. One table contains all data without links

Solution

1. Step 1: Understand one-to-one relationship meaning

   A one-to-one relationship means each record in one table corresponds to exactly one record in another table.

2. Step 2: Compare options to definition

   Each row in one table matches exactly one row in another table matches this definition perfectly, while others describe different relationships or no relationship.

3. Final Answer:

   Each row in one table matches exactly one row in another table -> Option A

4. Quick Check:

   One-to-one = single matching row [OK]

Hint: One-to-one means one row links to exactly one row [OK]

Common Mistakes:

• Confusing one-to-one with one-to-many
• Thinking tables have no relation
• Assuming one table holds all data

2. Which SQL constraint is commonly used to enforce a one-to-one relationship between two tables?

easy

A. FOREIGN KEY without UNIQUE

B. CHECK constraint on any column

C. UNIQUE constraint on the foreign key column

D. NOT NULL constraint on primary key

Solution

1. Step 1: Identify constraint enforcing uniqueness

   To ensure one-to-one, the foreign key must be unique so no duplicates link to the same row.

2. Step 2: Match constraints to this need

   UNIQUE constraint on the foreign key column enforces this, while FOREIGN KEY alone does not guarantee uniqueness.

3. Final Answer:

   UNIQUE constraint on the foreign key column -> Option C

4. Quick Check:

   Unique foreign key = one-to-one [OK]

Hint: Use UNIQUE on foreign key to enforce one-to-one [OK]

Common Mistakes:

• Using FOREIGN KEY without UNIQUE allows many-to-one
• Confusing CHECK with uniqueness
• Assuming NOT NULL enforces one-to-one

3. Given these tables:

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

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

What does the UNIQUE constraint on PersonID in Passport ensure?

medium

A. Each person can have multiple passports

B. Each passport belongs to exactly one person, and each person has at most one passport

C. PersonID can be null in Passport

D. PassportID can be duplicated

Solution

1. Step 1: Understand UNIQUE on PersonID in Passport

   The UNIQUE constraint means no two rows in Passport can have the same PersonID, so one person links to at most one passport.

2. Step 2: Analyze relationship enforced

   Since Passport has a foreign key to Person and PersonID is unique, each passport belongs to one person, and each person can have only one passport.

3. Final Answer:

   Each passport belongs to exactly one person, and each person has at most one passport -> Option B

4. Quick Check:

   Unique foreign key = one-to-one link [OK]

Hint: UNIQUE foreign key means one-to-one link [OK]

Common Mistakes:

• Thinking one person can have many passports
• Ignoring UNIQUE constraint effect
• Assuming null allowed without checking

4. Consider this table design:

CREATE TABLE Employee (
  EmployeeID INT PRIMARY KEY,
  Name VARCHAR(50)
);

CREATE TABLE EmployeeDetails (
  DetailID INT PRIMARY KEY,
  EmployeeID INT,
  Address VARCHAR(100),
  FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
);

What is missing to enforce a one-to-one relationship between Employee and EmployeeDetails?

medium

A. Add PRIMARY KEY on EmployeeID in EmployeeDetails

B. Add NOT NULL constraint on EmployeeID in EmployeeDetails

C. Remove FOREIGN KEY constraint

D. Add UNIQUE constraint on EmployeeID in EmployeeDetails

Solution

1. Step 1: Identify current constraints

   EmployeeDetails has a foreign key to Employee but no uniqueness on EmployeeID, so multiple details can link to one employee.

2. Step 2: Determine what enforces one-to-one

   Adding UNIQUE on EmployeeID ensures each employee links to at most one detail, enforcing one-to-one.

3. Final Answer:

   Add UNIQUE constraint on EmployeeID in EmployeeDetails -> Option D

4. Quick Check:

   Unique foreign key needed for one-to-one [OK]

Hint: Add UNIQUE on foreign key column for one-to-one [OK]

Common Mistakes:

• Assuming NOT NULL enforces one-to-one
• Removing foreign key breaks relationship
• Confusing primary key with foreign key uniqueness

5. You want to split user data into two tables: User and UserProfile. Each user has exactly one profile. Which design best enforces this one-to-one relationship?

hard

A. User and UserProfile share the same primary key column

B. UserProfile has a foreign key to User without UNIQUE constraint

C. UserProfile has no foreign key but a separate primary key

D. UserProfile has a foreign key to User with UNIQUE constraint on that foreign key

Solution

1. Step 1: Understand one-to-one enforcement methods

   One way is to share the same primary key in both tables, ensuring exactly one matching row.

2. Step 2: Compare options

   User and UserProfile share the same primary key column uses the same primary key in both tables, which is a strong one-to-one design. UserProfile has a foreign key to User with UNIQUE constraint on that foreign key is valid but less strict. Options B and C do not enforce one-to-one properly.

3. Final Answer:

   User and UserProfile share the same primary key column -> Option A

4. Quick Check:

   Shared primary key = strict one-to-one [OK]

Hint: Use shared primary key for strict one-to-one [OK]

Common Mistakes:

• Ignoring uniqueness on foreign key
• Assuming foreign key alone enforces one-to-one
• Not linking tables properly