One-to-one relationship design in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a one-to-one relationship in database design?

A one-to-one relationship means each record in one table matches exactly one record in another table, like a person having one passport.

intermediate

How do you enforce a one-to-one relationship between two tables in SQL?

Use a unique foreign key in one table that references the primary key of the other table, ensuring each key appears only once.

intermediate

Why might you split data into two tables with a one-to-one relationship?

To separate optional or sensitive data, improve organization, or optimize performance by loading only needed data.

beginner

Which SQL constraint ensures that a foreign key is unique in a one-to-one relationship?

The UNIQUE constraint on the foreign key column ensures no duplicates, enforcing one-to-one mapping.

intermediate

Example: How to create two tables with a one-to-one relationship between 'Person' and 'Passport'?

Create 'Person' with primary key 'person_id'. Create 'Passport' with 'passport_id' as primary key and 'person_id' as unique foreign key referencing 'Person'.

What does a one-to-one relationship mean in databases?

A Many records in one table match many records in another table

B One record in a table matches many records in another table

C Each record in one table matches exactly one record in another table

D No relationship between tables

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

Which SQL constraint is essential to enforce a one-to-one relationship?

A UNIQUE constraint on the foreign key

B DEFAULT constraint

C CHECK constraint

D NOT NULL constraint

The UNIQUE constraint on the foreign key column ensures each foreign key value appears only once, enforcing one-to-one.

In a one-to-one relationship, where is the foreign key usually placed?

A In the table with optional or additional data

B In both tables

C In the table with the primary key only

D Nowhere, foreign keys are not used

The foreign key is placed in the table holding optional or extra data to link back to the main table.

Why split data into two tables with a one-to-one relationship?

A To duplicate data

B To avoid using foreign keys

C To make queries slower

D To separate optional or sensitive data

Splitting helps organize data, keep sensitive info separate, and optimize performance.

Which SQL statement correctly creates a one-to-one relationship?

A CREATE TABLE Passport (passport_id INT PRIMARY KEY, person_id INT, FOREIGN KEY (person_id) REFERENCES Person(person_id));

B CREATE TABLE Passport (passport_id INT PRIMARY KEY, person_id INT UNIQUE, FOREIGN KEY (person_id) REFERENCES Person(person_id));

C CREATE TABLE Passport (passport_id INT PRIMARY KEY, person_id INT);

D CREATE TABLE Passport (passport_id INT PRIMARY KEY, person_id INT NOT NULL);

Option B adds UNIQUE to person_id foreign key, enforcing one-to-one relationship.

Explain how to design a one-to-one relationship between two tables in SQL.

Think about keys and uniqueness to link tables.

Why would you use a one-to-one relationship instead of combining all data in one table?

Consider reasons for splitting data logically.