Why constraints matter in SQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Effect of UNIQUE constraint on duplicate inserts

Consider a table users with a username column that has a UNIQUE constraint. What happens when you try to insert a duplicate username?

SQL

CREATE TABLE users (id INT PRIMARY KEY, username VARCHAR(50) UNIQUE);
INSERT INTO users VALUES (1, 'alice');
INSERT INTO users VALUES (2, 'alice');

A The second insert is ignored silently without error.

B The second insert fails with a UNIQUE constraint violation error.

C The second insert succeeds and creates a duplicate username.

D The second insert succeeds and overwrites the first row.

💡 Hint

Think about what UNIQUE means for a column.

A UNIQUE constraint prevents duplicate values in the column. Trying to insert a duplicate causes an error.

🧠 Conceptual

intermediate

Purpose of NOT NULL constraint

Why do we use the NOT NULL constraint on a database column?

A To speed up queries on that column.

B To allow the column to store multiple values in one cell.

C To automatically generate unique values for the column.

D To ensure the column always has a value and never stores NULL.

💡 Hint

Think about what NULL means in a database.

NOT NULL means the column must always have a value; it cannot be left empty (NULL).

📝 Syntax

advanced

Identify the correct FOREIGN KEY constraint syntax

Which option correctly adds a FOREIGN KEY constraint to the orders table referencing customers(id)?

SQL

ALTER TABLE orders ADD CONSTRAINT fk_customer_id ... ;

A FOREIGN KEY (customer_id) REFERENCES customers(id);

B FOREIGN KEY customer_id REFERENCES customers(id);

C FOREIGN KEY (customer_id) REF customers(id);

D FOREIGN KEY customer_id REF customers(id);

💡 Hint

Check the correct syntax for FOREIGN KEY in SQL.

The correct syntax requires parentheses around the column name and the keyword REFERENCES.

❓ query_result

advanced

Result of violating CHECK constraint

Given a table products with a CHECK constraint price > 0, what happens if you try to insert a product with price = -5?

SQL

CREATE TABLE products (id INT PRIMARY KEY, price DECIMAL CHECK (price > 0));
INSERT INTO products VALUES (1, -5);

A The insert succeeds but sets price to 0 automatically.

B The insert succeeds and stores the negative price.

C The insert fails with a CHECK constraint violation error.

D The insert is ignored silently without error.

💡 Hint

What does a CHECK constraint do?

CHECK constraints enforce conditions on column values. Violating them causes an error.

🔧 Debug

expert

Why does this PRIMARY KEY constraint fail?

Consider this table creation:

CREATE TABLE employees (
  id INT,
  name VARCHAR(100),
  PRIMARY KEY (name)
);

Why might this PRIMARY KEY constraint cause problems?

A Because names may not be unique, violating the PRIMARY KEY uniqueness requirement.

B Because PRIMARY KEY cannot be on a VARCHAR column.

C Because the table must have an explicit id as PRIMARY KEY.

D Because PRIMARY KEY columns cannot allow NULL values.

💡 Hint

Think about what PRIMARY KEY means for data uniqueness.

PRIMARY KEY requires unique values. Names may repeat, so using name as PRIMARY KEY can cause duplicate key errors.