Primary key declaration in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this query?

Given the table users with columns id INT and name VARCHAR(50), and id declared as the primary key, what will this query return?

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

A Error: Cannot check NULL on primary key

B 1

C NULL

D 0

💡 Hint

Primary keys cannot have NULL values.

Since the primary key column id cannot contain NULL values, the count of rows where id IS NULL is always zero.

📝 Syntax

intermediate

Which option correctly declares a primary key on the order_id column?

You want to create a table orders with a primary key on order_id. Which of the following CREATE TABLE statements is correct?

A CREATE TABLE orders (order_id INT PRIMARY KEY, order_date DATE);

B CREATE TABLE orders (order_id INT, order_date DATE, PRIMARY KEY (order_id));

C CREATE TABLE orders (order_id INT, order_date DATE, PRIMARY KEY order_id);

D CREATE TABLE orders (order_id INT, order_date DATE, PRIMARY order_id);

💡 Hint

The primary key constraint syntax requires parentheses around the column name when declared separately.

Option B correctly uses PRIMARY KEY (order_id) syntax to declare the primary key constraint on the column.

❓ schema

advanced

Which table schema correctly enforces a composite primary key on user_id and session_id?

You want to ensure that the combination of user_id and session_id is unique and not null. Which table definition achieves this?

A CREATE TABLE sessions (user_id INT, session_id INT, PRIMARY KEY (user_id, session_id));

B CREATE TABLE sessions (user_id INT, session_id INT UNIQUE, PRIMARY KEY (user_id));

C CREATE TABLE sessions (user_id INT PRIMARY KEY, session_id INT PRIMARY KEY);

D CREATE TABLE sessions (user_id INT, session_id INT, UNIQUE (user_id, session_id));

💡 Hint

Composite primary keys require listing all columns inside the PRIMARY KEY parentheses.

Option A correctly declares a composite primary key on both user_id and session_id, ensuring uniqueness and non-null for the pair.

🔧 Debug

advanced

Why does this table creation fail?

Consider this SQL statement:

CREATE TABLE products (product_id INT, name VARCHAR(100), PRIMARY KEY product_id);

Why does it cause an error?

A Missing parentheses around product_id in PRIMARY KEY declaration.

B product_id must be declared as NOT NULL explicitly.

C PRIMARY KEY cannot be declared after columns.

D VARCHAR(100) is invalid syntax.

💡 Hint

Check the syntax for PRIMARY KEY constraints.

The PRIMARY KEY declaration requires parentheses around the column name(s). Missing parentheses cause a syntax error.

❓ optimization

expert

Which primary key choice optimizes query speed for a large table with many lookups by email?

You have a large customers table. You want to optimize queries that search by email. Which primary key declaration is best?

A No primary key, just UNIQUE(email)

B PRIMARY KEY (customer_id)

C PRIMARY KEY (email)

D PRIMARY KEY (customer_id, email)

💡 Hint

Primary keys create clustered indexes that speed up lookups.

Declaring email as the primary key creates a clustered index on it, optimizing queries searching by email. Other options either index a different column or do not cluster on email.