FOREIGN KEY constraint in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this foreign key constraint check?

Consider these two tables:

CREATE TABLE Departments (
  DeptID INT PRIMARY KEY,
  DeptName VARCHAR(50)
);

CREATE TABLE Employees (
  EmpID INT PRIMARY KEY,
  EmpName VARCHAR(50),
  DeptID INT,
  FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

INSERT INTO Departments VALUES (1, 'HR'), (2, 'IT');
INSERT INTO Employees VALUES (101, 'Alice', 1), (102, 'Bob', 3);

What happens when inserting the second employee?

A The insert succeeds but a warning is shown about missing DeptID.

B The insert fails with a foreign key constraint violation error.

C The insert succeeds but DeptID is set to NULL for Bob.

D The insert succeeds and adds Bob with DeptID 3.

💡 Hint

Foreign keys must match existing values in the referenced table.

The foreign key constraint requires that DeptID in Employees must exist in Departments. Since 3 is not in Departments, the insert fails.

🧠 Conceptual

intermediate

Which statement about foreign keys is true?

Choose the correct statement about foreign key constraints in SQL.

A Foreign keys automatically delete rows in the referenced table when referenced.

B A foreign key column must always be unique in its own table.

C A foreign key enforces that values in one table must exist in another table.

D Foreign keys allow inserting any value regardless of the referenced table.

💡 Hint

Think about what referential integrity means.

Foreign keys ensure that values in the child table exist in the parent table, maintaining referential integrity.

📝 Syntax

advanced

Which option correctly defines a foreign key constraint?

Which SQL statement correctly adds a foreign key constraint to the Employees table referencing Departments?

A ALTER TABLE Employees ADD CONSTRAINT fk_dept FOREIGN KEY DeptID TO Departments(DeptID);

B ALTER TABLE Employees ADD FOREIGN KEY (DeptID) TO Departments(DeptID);

C ALTER TABLE Employees ADD FOREIGN KEY DeptID REFERENCES Departments(DeptID);

D ALTER TABLE Employees ADD CONSTRAINT fk_dept FOREIGN KEY (DeptID) REFERENCES Departments(DeptID);

💡 Hint

Check the syntax for adding constraints with names.

Option D uses correct syntax: ADD CONSTRAINT with a name, then FOREIGN KEY (column) REFERENCES table(column).

❓ optimization

advanced

How to optimize foreign key checks for bulk inserts?

You need to insert thousands of rows into a child table with a foreign key. Which approach optimizes performance?

A Disable foreign key checks before insert, then re-enable after all inserts.

B Drop the foreign key constraint permanently before inserts.

C Insert rows one by one to ensure foreign key checks run each time.

D Insert rows without any foreign key column values.

💡 Hint

Think about temporarily suspending checks to speed up bulk operations.

Disabling foreign key checks temporarily speeds up bulk inserts but requires care to maintain data integrity.

🔧 Debug

expert

Why does this foreign key constraint fail to create?

Given these tables:

CREATE TABLE Parents (
  ParentID INT PRIMARY KEY
);

CREATE TABLE Children (
  ChildID INT PRIMARY KEY,
  ParentID INT,
  FOREIGN KEY (ParentID) REFERENCES Parents(ParentID)
);

CREATE TABLE Grandchildren (
  GrandchildID INT PRIMARY KEY,
  ChildID INT,
  FOREIGN KEY (ChildID) REFERENCES Children(ChildID)
);

ALTER TABLE Grandchildren ADD CONSTRAINT fk_child FOREIGN KEY (ChildID) REFERENCES Children(ChildID);

The last ALTER TABLE statement fails. Why?

A The foreign key already exists; duplicate constraint causes error.

B The referenced column ChildID is not a primary key or unique in Children.

C Grandchildren table does not have a ChildID column.

D Parents table must be referenced before Children in foreign keys.

💡 Hint

Check if the foreign key constraint already exists.

The foreign key on ChildID in Grandchildren was defined inline during table creation, so adding it again causes a duplicate constraint error.

Practice

1. What is the main purpose of a FOREIGN KEY constraint in a database?

easy

A. To store large amounts of text data efficiently

B. To speed up database queries by creating indexes

C. To link two tables by ensuring values in one table match values in another

D. To automatically backup the database

Solution

1. Step 1: Understand the role of FOREIGN KEY

   A FOREIGN KEY connects columns in two tables to keep data related and consistent.

2. Step 2: Compare options with this role

   Only To link two tables by ensuring values in one table match values in another describes linking tables by matching values, which is the purpose of FOREIGN KEY.

3. Final Answer:

   To link two tables by ensuring values in one table match values in another -> Option C

4. Quick Check:

   FOREIGN KEY links tables = A [OK]

Hint: FOREIGN KEY links tables by matching columns [OK]

Common Mistakes:

• Confusing FOREIGN KEY with indexing
• Thinking FOREIGN KEY stores data
• Assuming FOREIGN KEY backs up data

2. Which of the following is the correct syntax to add a FOREIGN KEY constraint to an existing table Orders referencing Customers(CustomerID)?

easy

A. ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

B. ALTER TABLE Orders ADD PRIMARY KEY (CustomerID) REFERENCES Customers(CustomerID);

C. ALTER TABLE Orders ADD FOREIGN KEY CustomerID REFERENCES Customers(CustomerID);

D. ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) TO Customers(CustomerID);

Solution

1. Step 1: Recall correct ALTER TABLE syntax for FOREIGN KEY

   The correct syntax uses: ALTER TABLE table_name ADD FOREIGN KEY (column) REFERENCES other_table(column);

2. Step 2: Check each option

   ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID); matches the correct syntax exactly. Options A, B, and C have syntax errors or wrong keywords.

3. Final Answer:

   ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID); -> Option A

4. Quick Check:

   Correct ALTER TABLE FOREIGN KEY syntax = D [OK]

Hint: Use ADD FOREIGN KEY (col) REFERENCES table(col) syntax [OK]

Common Mistakes:

• Using PRIMARY KEY instead of FOREIGN KEY
• Omitting parentheses around column name
• Using TO instead of REFERENCES keyword

3. Given these tables:
CREATE TABLE Customers (CustomerID INT PRIMARY KEY, Name VARCHAR(50));
CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));
What happens if you try to insert INSERT INTO Orders (OrderID, CustomerID) VALUES (1, 999); when there is no customer with CustomerID = 999?

medium

A. The insert fails due to FOREIGN KEY constraint violation

B. The insert succeeds but CustomerID is set to NULL

C. The insert succeeds and adds the order with CustomerID 999

D. The insert succeeds but triggers a warning

Solution

1. Step 1: Understand FOREIGN KEY enforcement

   FOREIGN KEY requires the referenced value to exist in the parent table before inserting.

2. Step 2: Apply to the insert statement

   Since CustomerID 999 does not exist in Customers, the insert violates the FOREIGN KEY rule and fails.

3. Final Answer:

   The insert fails due to FOREIGN KEY constraint violation -> Option A

4. Quick Check:

   Insert with missing parent key = fails [OK]

Hint: Insert fails if referenced key doesn't exist [OK]

Common Mistakes:

• Assuming insert sets foreign key to NULL automatically
• Thinking insert triggers only warnings, not errors
• Believing insert succeeds without parent key

4. You have this table creation:
CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, FOREIGN KEY CustomerID REFERENCES Customers(CustomerID));
What is wrong with this statement?

medium

A. PRIMARY KEY cannot be used with FOREIGN KEY in the same table

B. CustomerID must be declared as PRIMARY KEY

C. REFERENCES keyword is not allowed in FOREIGN KEY constraints

D. FOREIGN KEY must be declared with parentheses around the column name

Solution

1. Step 1: Check FOREIGN KEY syntax

   FOREIGN KEY columns must be enclosed in parentheses, like FOREIGN KEY (CustomerID).

2. Step 2: Identify the error in the statement

   The statement misses parentheses around CustomerID in FOREIGN KEY declaration, causing syntax error.

3. Final Answer:

   FOREIGN KEY must be declared with parentheses around the column name -> Option D

4. Quick Check:

   FOREIGN KEY columns need parentheses [OK]

Hint: Always use parentheses around FOREIGN KEY columns [OK]

Common Mistakes:

• Omitting parentheses around foreign key columns
• Thinking PRIMARY KEY conflicts with FOREIGN KEY
• Misunderstanding REFERENCES usage

5. You want to delete a customer from Customers table who has orders in Orders table. The Orders table has a FOREIGN KEY on CustomerID referencing Customers(CustomerID) with ON DELETE CASCADE. What will happen when you delete that customer?

hard

A. The delete fails because orders exist for that customer

B. The customer is deleted and all their orders are automatically deleted

C. The customer is deleted but orders remain with invalid CustomerID

D. The delete succeeds but sets CustomerID in orders to NULL

Solution

1. Step 1: Understand ON DELETE CASCADE effect

   ON DELETE CASCADE means deleting a parent row also deletes all related child rows automatically.

2. Step 2: Apply to deleting a customer with orders

   Deleting the customer will also delete all orders linked by CustomerID in Orders table.

3. Final Answer:

   The customer is deleted and all their orders are automatically deleted -> Option B

4. Quick Check:

   ON DELETE CASCADE deletes related rows [OK]

Hint: ON DELETE CASCADE removes child rows with parent [OK]

Common Mistakes:

• Assuming delete fails due to existing child rows
• Thinking child rows remain with broken references
• Confusing CASCADE with SET NULL behavior