0
0
SQLquery~20 mins

FOREIGN KEY constraint in SQL - Practice Problems & Coding Challenges

Choose your learning style9 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Challenge - 5 Problems
🎖️
Foreign Key Master
Get all challenges correct to earn this badge!
Test your skills under time pressure!
❓ query_result
intermediate
2:00remaining
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?

AThe insert succeeds but a warning is shown about missing DeptID.
BThe insert fails with a foreign key constraint violation error.
CThe insert succeeds but DeptID is set to NULL for Bob.
DThe insert succeeds and adds Bob with DeptID 3.
Attempts:
2 left
💡 Hint

Foreign keys must match existing values in the referenced table.

🧠 Conceptual
intermediate
1:30remaining
Which statement about foreign keys is true?

Choose the correct statement about foreign key constraints in SQL.

AForeign keys automatically delete rows in the referenced table when referenced.
BA foreign key column must always be unique in its own table.
CA foreign key enforces that values in one table must exist in another table.
DForeign keys allow inserting any value regardless of the referenced table.
Attempts:
2 left
💡 Hint

Think about what referential integrity means.

📝 Syntax
advanced
2:00remaining
Which option correctly defines a foreign key constraint?

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

AALTER TABLE Employees ADD CONSTRAINT fk_dept FOREIGN KEY DeptID TO Departments(DeptID);
BALTER TABLE Employees ADD FOREIGN KEY (DeptID) TO Departments(DeptID);
CALTER TABLE Employees ADD FOREIGN KEY DeptID REFERENCES Departments(DeptID);
DALTER TABLE Employees ADD CONSTRAINT fk_dept FOREIGN KEY (DeptID) REFERENCES Departments(DeptID);
Attempts:
2 left
💡 Hint

Check the syntax for adding constraints with names.

❓ optimization
advanced
2:00remaining
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?

ADisable foreign key checks before insert, then re-enable after all inserts.
BDrop the foreign key constraint permanently before inserts.
CInsert rows one by one to ensure foreign key checks run each time.
DInsert rows without any foreign key column values.
Attempts:
2 left
💡 Hint

Think about temporarily suspending checks to speed up bulk operations.

🔧 Debug
expert
2:30remaining
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?

AThe foreign key already exists; duplicate constraint causes error.
BThe referenced column ChildID is not a primary key or unique in Children.
CGrandchildren table does not have a ChildID column.
DParents table must be referenced before Children in foreign keys.
Attempts:
2 left
💡 Hint

Check if the foreign key constraint already exists.