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.

Practice

1. Which of the following is TRUE about a PRIMARY KEY in a SQL table?

easy

A. It allows duplicate values but no NULLs.

B. It uniquely identifies each row and cannot contain NULL values.

C. It can contain multiple NULL values.

D. It is used only for indexing and does not enforce uniqueness.

Solution

1. Step 1: Understand the role of PRIMARY KEY

   A PRIMARY KEY uniquely identifies each row in a table and does not allow NULL values to ensure every row is distinct.

2. Step 2: Compare options with PRIMARY KEY properties

   It uniquely identifies each row and cannot contain NULL values. correctly states uniqueness and no NULLs. Other options contradict these rules.

3. Final Answer:

   It uniquely identifies each row and cannot contain NULL values. -> Option B

4. Quick Check:

   PRIMARY KEY = unique + no NULL [OK]

Hint: Primary key = unique + no NULL values [OK]

Common Mistakes:

• Thinking primary keys allow NULLs
• Confusing primary key with unique constraint
• Assuming primary key allows duplicates

2. Which SQL statement correctly defines a column id as a primary key in a table?

easy

A. CREATE TABLE users (id INT, name VARCHAR(50) PRIMARY KEY);

B. CREATE TABLE users (id INT UNIQUE, name VARCHAR(50));

C. CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));

D. CREATE TABLE users (id INT NOT NULL, name VARCHAR(50));

Solution

1. Step 1: Identify correct PRIMARY KEY syntax

   The correct syntax to define a primary key inline is column_name data_type PRIMARY KEY.

2. Step 2: Check each option

   CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50)); uses correct syntax. CREATE TABLE users (id INT UNIQUE, name VARCHAR(50)); uses UNIQUE, not PRIMARY KEY. CREATE TABLE users (id INT NOT NULL, name VARCHAR(50)); only uses NOT NULL. CREATE TABLE users (id INT, name VARCHAR(50) PRIMARY KEY); incorrectly assigns PRIMARY KEY to the wrong column.

3. Final Answer:

   CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50)); -> Option C

4. Quick Check:

   PRIMARY KEY syntax = column_name data_type PRIMARY KEY [OK]

Hint: PRIMARY KEY goes right after column type [OK]

Common Mistakes:

• Using UNIQUE instead of PRIMARY KEY
• Placing PRIMARY KEY on wrong column
• Omitting PRIMARY KEY keyword

3. Given the table employees with id as PRIMARY KEY, what happens if you run this SQL?

INSERT INTO employees (id, name) VALUES (1, 'Alice');
INSERT INTO employees (id, name) VALUES (1, 'Bob');

medium

A. The second insert fails due to duplicate primary key.

B. Both rows are inserted successfully.

C. The second insert overwrites the first row.

D. Both inserts fail because primary keys cannot be integers.

Solution

1. Step 1: Understand PRIMARY KEY uniqueness constraint

   PRIMARY KEY enforces unique values. The first insert adds id=1 successfully.

2. Step 2: Analyze second insert with duplicate id

   The second insert tries to add id=1 again, violating uniqueness, causing an error.

3. Final Answer:

   The second insert fails due to duplicate primary key. -> Option A

4. Quick Check:

   Duplicate PRIMARY KEY value causes insert failure [OK]

Hint: Duplicate primary keys cause insert errors [OK]

Common Mistakes:

• Assuming duplicates are allowed in primary key
• Thinking second insert overwrites first
• Believing primary keys cannot be integers

4. You have this table definition:

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

Which problem will occur if you try to insert two rows with product_id as NULL?

medium

A. Both rows insert successfully because UNIQUE allows multiple NULLs.

B. The second insert fails because UNIQUE does not allow duplicate NULLs.

C. Syntax error because NULL is not allowed in UNIQUE columns.

D. Both inserts fail because product_id is not a PRIMARY KEY.

Solution

1. Step 1: Understand UNIQUE constraint with NULLs

   UNIQUE allows only one NULL in some databases, but standard SQL allows multiple NULLs because NULL means unknown.

2. Step 2: Apply to given inserts

   In most SQL systems, inserting multiple rows with NULL in a UNIQUE column succeeds because NULLs are not considered equal.

3. Final Answer:

   Both rows insert successfully because UNIQUE allows multiple NULLs. -> Option A

4. Quick Check:

   UNIQUE allows multiple NULLs in most SQL [OK]

Hint: UNIQUE allows multiple NULLs, PRIMARY KEY does not [OK]

Common Mistakes:

• Assuming UNIQUE disallows multiple NULLs
• Confusing UNIQUE with PRIMARY KEY NULL rules
• Expecting syntax error on NULL in UNIQUE

5. You want to create a table orders where each order has a unique order_id and a unique tracking_number that can be NULL. Which SQL definition is correct?

hard

A. CREATE TABLE orders (order_id INT UNIQUE, tracking_number VARCHAR(20) PRIMARY KEY);

B. CREATE TABLE orders (order_id INT, tracking_number VARCHAR(20) UNIQUE PRIMARY KEY);

C. CREATE TABLE orders (order_id INT PRIMARY KEY UNIQUE, tracking_number VARCHAR(20) NOT NULL UNIQUE);

D. CREATE TABLE orders (order_id INT PRIMARY KEY, tracking_number VARCHAR(20) UNIQUE);

Solution

1. Step 1: Define order_id as PRIMARY KEY

   order_id must be unique and not NULL, so PRIMARY KEY is appropriate.

2. Step 2: Define tracking_number as UNIQUE and nullable

   tracking_number should be unique but can be NULL, so UNIQUE constraint without NOT NULL is correct.

3. Step 3: Evaluate options

   CREATE TABLE orders (order_id INT PRIMARY KEY, tracking_number VARCHAR(20) UNIQUE); matches requirements. CREATE TABLE orders (order_id INT UNIQUE, tracking_number VARCHAR(20) PRIMARY KEY); wrongly assigns PRIMARY KEY to tracking_number. CREATE TABLE orders (order_id INT PRIMARY KEY UNIQUE, tracking_number VARCHAR(20) NOT NULL UNIQUE); makes tracking_number NOT NULL, disallowing NULLs. CREATE TABLE orders (order_id INT, tracking_number VARCHAR(20) UNIQUE PRIMARY KEY); has invalid syntax combining UNIQUE and PRIMARY KEY on tracking_number.

4. Final Answer:

   CREATE TABLE orders (order_id INT PRIMARY KEY, tracking_number VARCHAR(20) UNIQUE); -> Option D

5. Quick Check:

   PRIMARY KEY unique + NOT NULL, UNIQUE allows NULL [OK]

Hint: PRIMARY KEY = unique+not null; UNIQUE allows NULL [OK]

Common Mistakes:

• Making UNIQUE column NOT NULL when NULLs allowed
• Assigning PRIMARY KEY to wrong column
• Combining UNIQUE and PRIMARY KEY on same column incorrectly