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

Need a 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

Need a 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
);

Need a 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

Need a hint?

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