Foreign key linking mental model in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding foreign key relationships

Imagine you have two tables: Orders and Customers. The Orders table has a column customer_id that links to the id column in Customers. What is the main purpose of this foreign key?

A To ensure every order is linked to a valid customer in the Customers table

B To store duplicate customer information inside the Orders table

C To speed up queries by copying customer data into Orders

D To allow orders to exist without any customer linked

💡 Hint

Think about how foreign keys help keep data consistent between tables.

The foreign key customer_id in Orders ensures that each order references a valid customer in the Customers table. This maintains data integrity by preventing orders from linking to non-existent customers.

❓ query_result

intermediate

Result of a join using foreign key

Given these tables:

Customers(id, name)
Orders(id, customer_id, product)

What will this query return?

SELECT Customers.name, Orders.product
FROM Orders
JOIN Customers ON Orders.customer_id = Customers.id;

A An error because the join condition is wrong

B A list of all customers, even those without orders

C Only orders without matching customers

D A list of customer names with the products they ordered

💡 Hint

Think about what a JOIN does when matching keys between tables.

The JOIN matches each order to its customer using the foreign key. The result shows customer names alongside their ordered products.

📝 Syntax

advanced

Correct foreign key syntax

Which option correctly defines a foreign key constraint in SQL?

SQL

CREATE TABLE Orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES Customers(id)
);

A FOREIGN KEY (customer_id) REFERENCES Customers(id);

B FOREIGN KEY customer_id -> Customers(id);

C FOREIGN KEY (customer_id) REFERENCES Customers(id)

D FOREIGN KEY customer_id REFERENCES Customers(id);

💡 Hint

Check the parentheses and semicolon placement in foreign key syntax.

The correct syntax requires parentheses around the foreign key column and no semicolon inside the table definition line. The semicolon ends the whole statement.

❓ optimization

advanced

Improving query performance with foreign keys

You have a large Orders table linked to Customers by a foreign key. Which action will best improve join query speed?

A Use SELECT * instead of specific columns

B Add an index on <code>Orders.customer_id</code>

C Add duplicate customer names in Orders

D Remove the foreign key constraint

💡 Hint

Think about how databases find matching rows quickly.

Indexing the foreign key column helps the database quickly find matching rows during joins, improving performance.

🔧 Debug

expert

Identifying foreign key constraint error

Given these tables:

CREATE TABLE Customers (id INT PRIMARY KEY, name TEXT);
CREATE TABLE Orders (id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES Customers(id));

What error occurs when running this?

INSERT INTO Orders (id, customer_id) VALUES (1, 999);

A Foreign key constraint violation because customer_id 999 does not exist in Customers

B Syntax error due to missing quotes around 999

C No error, row inserted successfully

D Primary key violation on Orders.id

💡 Hint

Consider what happens if you insert a foreign key value not present in the referenced table.

The foreign key constraint prevents inserting a customer_id that does not exist in Customers, causing a violation error.

Practice

1. What is the main purpose of a foreign key in a database?

easy

A. To link one table to another and ensure data consistency

B. To store large amounts of text data

C. To speed up database queries

D. To create a backup of the database

Solution

1. Step 1: Understand the role of foreign keys

   A foreign key connects one table to another by referencing a primary key in the related table.

2. Step 2: Identify the purpose of this connection

   This connection helps keep data consistent and organized by preventing invalid data entries.

3. Final Answer:

   To link one table to another and ensure data consistency -> Option A

4. Quick Check:

   Foreign key = link tables + data consistency [OK]

Hint: Foreign keys link tables to keep data correct [OK]

Common Mistakes:

• Thinking foreign keys store data themselves
• Confusing foreign keys with indexes
• Believing foreign keys speed up queries directly

2. Which of the following is the correct syntax to declare a foreign key in SQL?

easy

A. FOREIGN KEY column_name REFERENCES other_table

B. PRIMARY KEY (column_name) REFERENCES other_table(other_column)

C. FOREIGN KEY (column_name) REFERENCES other_table(other_column)

D. KEY FOREIGN (column_name) REFERENCES other_table(other_column)

Solution

1. Step 1: Recall the standard foreign key syntax

   The correct syntax includes the keywords FOREIGN KEY, the column in parentheses, then REFERENCES followed by the referenced table and column in parentheses.

2. Step 2: Compare options to syntax

   FOREIGN KEY (column_name) REFERENCES other_table(other_column) matches the correct syntax exactly. Other options have wrong keyword order or missing parentheses.

3. Final Answer:

   FOREIGN KEY (column_name) REFERENCES other_table(other_column) -> Option C

4. Quick Check:

   FOREIGN KEY + REFERENCES + (table.column) = A [OK]

Hint: FOREIGN KEY (col) REFERENCES table(col) is correct syntax [OK]

Common Mistakes:

• Omitting parentheses around column names
• Swapping PRIMARY KEY with FOREIGN KEY
• Incorrect keyword order

3. Given these tables:
CREATE TABLE Authors (AuthorID INT PRIMARY KEY, Name VARCHAR(50));
CREATE TABLE Books (BookID INT PRIMARY KEY, Title VARCHAR(100), AuthorID INT, FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID));
What happens if you try to insert INSERT INTO Books (BookID, Title, AuthorID) VALUES (1, 'My Book', 99); when there is no author with AuthorID = 99 in Authors?

medium

A. The insert succeeds but AuthorID is set to NULL

B. The insert fails due to foreign key constraint violation

C. The database creates a new author with AuthorID 99 automatically

D. The insert succeeds and adds the book

Solution

1. Step 1: Understand foreign key constraint behavior

   A foreign key requires that the referenced value exists in the parent table to maintain data integrity.

2. Step 2: Apply this to the insert statement

   Since AuthorID 99 does not exist in Authors, the insert violates the foreign key constraint and fails.

3. Final Answer:

   The insert fails due to foreign key constraint violation -> Option B

4. Quick Check:

   Foreign key requires existing parent row = D [OK]

Hint: Foreign key insert fails if parent key missing [OK]

Common Mistakes:

• Assuming automatic creation of missing parent rows
• Thinking insert will succeed with NULL foreign key
• Ignoring foreign key constraints

4. Consider 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. Foreign key cannot reference Customers table

B. CustomerID should be declared as PRIMARY KEY

C. PRIMARY KEY must be declared after FOREIGN KEY

D. Missing parentheses around the foreign key column name

Solution

1. Step 1: Check foreign key syntax

   The foreign key column name must be enclosed in parentheses after FOREIGN KEY.

2. Step 2: Identify the error in the statement

   The statement uses FOREIGN KEY CustomerID without parentheses, which is invalid syntax.

3. Final Answer:

   Missing parentheses around the foreign key column name -> Option D

4. Quick Check:

   FOREIGN KEY (col) needs parentheses [OK]

Hint: Always use parentheses around foreign key columns [OK]

Common Mistakes:

• Omitting parentheses in FOREIGN KEY declaration
• Misordering PRIMARY and FOREIGN KEY declarations
• Confusing foreign key with primary key requirements

5. You have 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) ON DELETE SET NULL);
If a department is deleted, what happens to employees linked to that department?

hard

A. Their DeptID is set to NULL automatically

B. The delete is blocked and fails

C. Employees linked to that department are deleted

D. Nothing happens; DeptID remains unchanged

Solution

1. Step 1: Understand ON DELETE SET NULL behavior

   This option means when the referenced row is deleted, the foreign key column in dependent rows is set to NULL.

2. Step 2: Apply to Employees and Departments

   Deleting a department sets DeptID to NULL in Employees who referenced it, keeping employees but removing the link.

3. Final Answer:

   Their DeptID is set to NULL automatically -> Option A

4. Quick Check:

   ON DELETE SET NULL means foreign keys become NULL [OK]

Hint: ON DELETE SET NULL clears foreign keys on delete [OK]

Common Mistakes:

• Assuming delete blocks or cascades employees
• Thinking employees get deleted automatically
• Ignoring ON DELETE action effects