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.