Composite primary keys in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a query on a table with composite primary key

Consider a table Orders with a composite primary key on (OrderID, ProductID). The table has the following rows:

OrderID | ProductID | Quantity
--------+-----------+---------
1       | 101       | 2
1       | 102       | 1
2       | 101       | 5
2       | 103       | 3

What will be the output of this query?

SELECT OrderID, ProductID FROM Orders WHERE OrderID = 1;

SQL

SELECT OrderID, ProductID FROM Orders WHERE OrderID = 1;

A [{"OrderID":1,"ProductID":101},{"OrderID":1,"ProductID":102}]

B [{"OrderID":1,"ProductID":101}]

C [{"OrderID":1,"ProductID":102},{"OrderID":2,"ProductID":101}]

D []

💡 Hint

Remember that the composite primary key means each combination of OrderID and ProductID is unique, but multiple rows can share the same OrderID.

The query filters rows where OrderID = 1. There are two such rows with ProductID 101 and 102 respectively, so both are returned.

🧠 Conceptual

intermediate

Understanding composite primary keys uniqueness

Which statement best describes the uniqueness enforced by a composite primary key on columns (A, B)?

A Each value in column A must be unique across the table.

B Either column A or column B must have unique values, but not necessarily both.

C Each combination of values in columns A and B must be unique across the table.

D Each value in column B must be unique across the table.

💡 Hint

Think about how composite keys combine columns to enforce uniqueness.

A composite primary key requires that the pair (A, B) is unique for every row, but individual columns can have duplicates.

📝 Syntax

advanced

Correct syntax to define a composite primary key

Which of the following SQL statements correctly defines a composite primary key on columns user_id and role_id in a table UserRoles?

A CREATE TABLE UserRoles (user_id INT PRIMARY KEY, role_id INT PRIMARY KEY);

B CREATE TABLE UserRoles (user_id INT, role_id INT, PRIMARY KEY (user_id, role_id));

C CREATE TABLE UserRoles (user_id INT, role_id INT, PRIMARY KEY user_id, role_id);

D CREATE TABLE UserRoles (user_id INT, role_id INT, PRIMARY KEY user_id AND role_id);

💡 Hint

Look for the correct syntax to declare multiple columns as a single primary key.

Option B correctly uses parentheses to list columns for the composite primary key. Options B, C, and D have syntax errors or misuse keywords.

❓ optimization

advanced

Indexing considerations with composite primary keys

You have a table with a composite primary key on (customer_id, order_id). You frequently query the table filtering only by order_id. What is the best indexing strategy to optimize these queries?

A Create a composite index on <code>(order_id, customer_id)</code>.

B Drop the primary key and create separate primary keys on each column.

C Rely only on the composite primary key index on <code>(customer_id, order_id)</code>.

D Create an additional index on <code>order_id</code> alone.

💡 Hint

Think about how indexes work with leading columns in composite keys.

The composite primary key index is ordered by customer_id first, so queries filtering only by order_id cannot efficiently use it. Creating a separate index on order_id improves performance.

🔧 Debug

expert

Identify the error in composite primary key constraint

Given the following table creation statement, what error will occur?

CREATE TABLE Enrollment (
  student_id INT,
  course_id INT,
  PRIMARY KEY student_id, course_id
);

A SyntaxError: Missing parentheses around columns in PRIMARY KEY definition.

B No error; table created successfully with composite primary key.

C TypeError: Columns must be declared as NOT NULL for primary key.

D Runtime error: Duplicate key values not allowed.

💡 Hint

Check the syntax for declaring composite primary keys.

The PRIMARY KEY clause requires parentheses around the column list. Omitting them causes a syntax error.