PRIMARY KEY constraint in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this query on a table with PRIMARY KEY?

Given a table Users with a PRIMARY KEY on column user_id, what will happen if you try to insert two rows with the same user_id?

SQL

CREATE TABLE Users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50)
);

INSERT INTO Users (user_id, username) VALUES (1, 'Alice');
INSERT INTO Users (user_id, username) VALUES (1, 'Bob');

A The second insert will fail with a duplicate key error.

B Both inserts will succeed, creating two rows with user_id = 1.

C The second insert will overwrite the first row with user_id = 1.

D The database will ignore the second insert silently.

💡 Hint

PRIMARY KEY enforces uniqueness for the column.

A PRIMARY KEY column must have unique values. Trying to insert a duplicate value causes an error.

🧠 Conceptual

intermediate

Which statement about PRIMARY KEY is true?

Choose the correct statement about the PRIMARY KEY constraint in SQL.

A PRIMARY KEY columns cannot contain NULL values.

B A table can have multiple PRIMARY KEY columns.

C PRIMARY KEY allows duplicate values but no NULLs.

D PRIMARY KEY columns can contain NULL values if specified.

💡 Hint

Think about uniqueness and nullability rules for PRIMARY KEY.

PRIMARY KEY columns must be unique and cannot contain NULL values.

📝 Syntax

advanced

Which CREATE TABLE statement correctly defines a composite PRIMARY KEY?

Select the valid SQL syntax to create a table with a composite PRIMARY KEY on columns order_id and product_id.

A

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

B

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

C

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

D

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

💡 Hint

Composite keys are defined with parentheses and commas inside PRIMARY KEY clause.

Option B correctly uses parentheses and commas to define a composite PRIMARY KEY.

🔧 Debug

advanced

Why does this table creation fail?

This SQL code fails to create the table. What is the reason?

SQL

CREATE TABLE Employees (
  emp_id INT,
  emp_name VARCHAR(100),
  PRIMARY KEY emp_id
);

A emp_name must be part of the PRIMARY KEY.

B emp_id cannot be PRIMARY KEY because it is INT type.

C PRIMARY KEY clause is missing parentheses around emp_id.

D PRIMARY KEY cannot be defined inside CREATE TABLE.

💡 Hint

Check the syntax for PRIMARY KEY declaration.

PRIMARY KEY columns must be enclosed in parentheses even if only one column is specified.

❓ optimization

expert

How does a PRIMARY KEY affect query performance?

Which statement best describes how a PRIMARY KEY constraint impacts database query performance?

A PRIMARY KEY forces full table scans for all queries involving that column.

B PRIMARY KEY slows down queries because it adds extra checks on every SELECT.

C PRIMARY KEY has no effect on query performance, only on data integrity.

D PRIMARY KEY automatically creates a unique index, speeding up searches on that column.

💡 Hint

Think about how databases use indexes.

PRIMARY KEY creates a unique index that helps the database find rows faster when searching by that key.