Primary keys and uniqueness in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this query on a table with a primary key?

Consider a table Users with columns id (primary key) and name. What will this query return?

SELECT COUNT(*) FROM Users WHERE id IS NULL;

A Number of rows in Users

B NULL

C 0

D Error: Cannot check NULL on primary key

💡 Hint

Primary keys cannot be NULL by definition.

Primary key columns must have unique, non-null values. Therefore, no row can have id as NULL, so the count is zero.

🧠 Conceptual

intermediate

Which statement about UNIQUE constraints is true?

Choose the correct statement about UNIQUE constraints in SQL.

A A UNIQUE constraint automatically creates a primary key.

B A UNIQUE constraint allows multiple NULL values in the column.

C A UNIQUE constraint prevents any NULL values in the column.

D A UNIQUE constraint allows duplicate values but only one NULL.

💡 Hint

Think about how NULL values are treated in UNIQUE columns.

UNIQUE constraints allow multiple NULLs because NULL is not considered equal to another NULL in SQL.

📝 Syntax

advanced

Which option correctly defines a composite primary key?

Given a table Orders with columns order_id and product_id, which SQL statement correctly defines a composite primary key on both columns?

A CREATE TABLE Orders (order_id INT, product_id INT, PRIMARY KEY (order_id, product_id));

B CREATE TABLE Orders (order_id INT, product_id INT, UNIQUE (order_id, product_id));

C CREATE TABLE Orders (order_id INT PRIMARY KEY, product_id INT PRIMARY KEY);

D CREATE TABLE Orders (order_id INT, product_id INT, PRIMARY KEY order_id, product_id);

💡 Hint

Composite primary keys list columns inside parentheses after PRIMARY KEY.

Option A correctly defines a composite primary key on both columns. Option A tries to assign two primary keys which is invalid. Option A defines a unique constraint, not a primary key. Option A has incorrect syntax.

🔧 Debug

advanced

Why does this table creation fail?

Consider this SQL statement:

CREATE TABLE Employees (id INT, email VARCHAR(100) UNIQUE, PRIMARY KEY (id, email));

Why does this statement cause an error?

A Because email cannot be both UNIQUE and part of the PRIMARY KEY.

B Because PRIMARY KEY cannot include a UNIQUE column.

C Because a column cannot be declared UNIQUE and also be part of a composite PRIMARY KEY.

D Because declaring UNIQUE on email is redundant when it is part of the PRIMARY KEY.

💡 Hint

Think about the uniqueness guarantees of primary keys.

Declaring email UNIQUE is redundant because the PRIMARY KEY constraint already enforces uniqueness on all columns in the key.

❓ optimization

expert

Which index is most efficient for enforcing uniqueness on a large table?

You have a large table Products with a column sku that must be unique. Which option is the most efficient way to enforce uniqueness and optimize lookups?

A Create a PRIMARY KEY on <code>sku</code> if it is the only key.

B Create a non-unique index on <code>sku</code> and check uniqueness in application code.

C Create a UNIQUE index on the <code>sku</code> column.

D Create a UNIQUE constraint without an index.

💡 Hint

Primary keys automatically create unique indexes and optimize lookups.

Defining sku as the PRIMARY KEY creates a unique index and optimizes queries. UNIQUE constraints also create indexes, but PRIMARY KEY is preferred if sku is the main identifier.