One-to-one relationship design in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a table with a primary key.

SQL

CREATE TABLE Person (PersonID [1] PRIMARY KEY, Name VARCHAR(100));

Drag options to blanks, or click blank then click option'

AIDENTITY

BINT

CDATE

DVARCHAR

Attempts:

3 left

💡 Hint

Common Mistakes

Using VARCHAR instead of INT for the primary key.

Using DATE type for an ID column.

The column PersonID should be of type INT to store integer IDs.

2fill in blank

medium

Complete the code to create a table with a foreign key referencing PersonID.

SQL

CREATE TABLE Passport (PassportID INT PRIMARY KEY, PersonID INT [1] NULL, FOREIGN KEY (PersonID) REFERENCES Person(PersonID));

Drag options to blanks, or click blank then click option'

ANOT

BUNIQUE

CNULL

DDEFAULT

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving the foreign key column nullable.

Using UNIQUE keyword incorrectly here.

The PersonID column should be NOT NULL to ensure every passport is linked to a person.

3fill in blank

hard

Fix the error in the code to enforce one-to-one relationship by adding a constraint.

SQL

ALTER TABLE Passport ADD CONSTRAINT [1] UNIQUE (PersonID);

Drag options to blanks, or click blank then click option'

Afk_person

Bchk_personid

Cpk_passport

Duq_personid

Attempts:

3 left

💡 Hint

Common Mistakes

Using a foreign key name for a unique constraint.

Using a check constraint name instead of unique.

The UNIQUE constraint on PersonID ensures one-to-one relationship by preventing duplicates.

4fill in blank

hard

Fill both blanks to create a one-to-one relationship where PassportID is also the foreign key.

SQL

CREATE TABLE Passport (PassportID INT [1] PRIMARY KEY, FOREIGN KEY (PassportID) [2] REFERENCES Person(PersonID));

Drag options to blanks, or click blank then click option'

ANOT NULL

BUNIQUE

CON DELETE CASCADE

DON UPDATE CASCADE

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving PassportID nullable.

Not using ON DELETE CASCADE for dependent records.

PassportID must be NOT NULL and the foreign key uses ON DELETE CASCADE to remove passports if the person is deleted.

5fill in blank

hard

Fill all three blanks to insert a new person and their passport ensuring the one-to-one relationship.

SQL

INSERT INTO Person (PersonID, Name) VALUES ([1], 'Alice');
INSERT INTO Passport (PassportID, PersonID) VALUES ([2], [3]);

Drag options to blanks, or click blank then click option'

A1

B1001

D2

Attempts:

3 left

💡 Hint

Common Mistakes

Using different PersonID values in Passport.

Using same values for PassportID and PersonID.

PersonID is 1, PassportID is 1001, and Passport.PersonID matches PersonID 1 to keep one-to-one link.