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.

Practice

1. Why are constraints important in a database?

easy

A. They make the database run faster by skipping checks.

B. They automatically delete old data to save space.

C. They allow users to enter any data without restrictions.

D. They ensure data accuracy and prevent invalid data entry.

Solution

1. Step 1: Understand the role of constraints

   Constraints are rules that keep data correct and reliable by preventing wrong data from being saved.

2. Step 2: Compare options with constraint purpose

   Only They ensure data accuracy and prevent invalid data entry. correctly states that constraints ensure data accuracy and prevent invalid data entry.

3. Final Answer:

   They ensure data accuracy and prevent invalid data entry. -> Option D

4. Quick Check:

   Constraints = Data accuracy and validity [OK]

Hint: Constraints keep data clean and correct [OK]

Common Mistakes:

• Thinking constraints speed up database by skipping checks
• Believing constraints allow any data without limits
• Assuming constraints delete data automatically

2. Which of the following is the correct syntax to add a NOT NULL constraint to a column named email in SQL?

easy

A. ALTER TABLE users ALTER COLUMN email SET NOT NULL;

B. ALTER TABLE users ADD CONSTRAINT email NOT NULL;

C. ALTER TABLE users MODIFY email NOT NULL;

D. ALTER TABLE users CHANGE email TO NOT NULL;

Solution

1. Step 1: Recall SQL syntax for NOT NULL constraint

   To set a column NOT NULL, the standard syntax is ALTER TABLE table ALTER COLUMN column SET NOT NULL.

2. Step 2: Match syntax with options

   ALTER TABLE users ALTER COLUMN email SET NOT NULL; matches the correct syntax. Options A, B, and D use incorrect or invalid syntax.

3. Final Answer:

   ALTER TABLE users ALTER COLUMN email SET NOT NULL; -> Option A

4. Quick Check:

   ALTER COLUMN ... SET NOT NULL = Correct syntax [OK]

Hint: Use ALTER COLUMN ... SET NOT NULL to add NOT NULL [OK]

Common Mistakes:

• Using ADD CONSTRAINT without naming the constraint
• Using MODIFY or CHANGE which are not standard SQL
• Confusing syntax with other SQL dialects

3. Given the table products(id INT PRIMARY KEY, price DECIMAL CHECK (price > 0)), what happens if you run this query?

INSERT INTO products (id, price) VALUES (1, -10);

medium

A. The row is inserted with price -10.

B. The query fails due to the CHECK constraint violation.

C. The price is automatically changed to 0.

D. The query succeeds but price is set to NULL.

Solution

1. Step 1: Understand the CHECK constraint on price

   The CHECK constraint requires price to be greater than 0, so negative values are not allowed.

2. Step 2: Analyze the INSERT statement

   Inserting price = -10 violates the CHECK constraint, so the database rejects the insert and throws an error.

3. Final Answer:

   The query fails due to the CHECK constraint violation. -> Option B

4. Quick Check:

   CHECK constraint rejects invalid data [OK]

Hint: CHECK constraints block invalid values on insert [OK]

Common Mistakes:

• Assuming invalid data is inserted anyway
• Thinking the database auto-corrects invalid values
• Believing NULL is set automatically on violation

4. You have this SQL statement to add a UNIQUE constraint:

ALTER TABLE users ADD UNIQUE (username);

But it fails with an error. What is the most likely cause?

medium

A. The username column contains duplicate values already.

B. The UNIQUE keyword is misspelled.

C. The table users does not exist.

D. UNIQUE constraints cannot be added after table creation.

Solution

1. Step 1: Understand UNIQUE constraint requirements

   Adding a UNIQUE constraint requires all existing values in the column to be unique.

2. Step 2: Identify why ALTER TABLE fails

   If duplicates exist in username, the database rejects the constraint addition with an error.

3. Final Answer:

   The username column contains duplicate values already. -> Option A

4. Quick Check:

   Existing duplicates block UNIQUE constraint [OK]

Hint: Check for duplicates before adding UNIQUE constraint [OK]

Common Mistakes:

• Assuming UNIQUE keyword spelling error
• Ignoring existing duplicate data
• Thinking UNIQUE can't be added after creation

5. You want to ensure that a birthdate column in a persons table only accepts dates in the past. Which constraint would best enforce this rule?

hard

A. UNIQUE (birthdate)

B. NOT NULL

C. CHECK (birthdate < CURRENT_DATE)

D. PRIMARY KEY (birthdate)

Solution

1. Step 1: Identify constraint to limit date values

   To restrict birthdate to past dates, a CHECK constraint comparing birthdate to CURRENT_DATE is needed.

2. Step 2: Evaluate options

   CHECK (birthdate < CURRENT_DATE) uses CHECK (birthdate < CURRENT_DATE), which enforces the rule. NOT NULL only requires a value, UNIQUE and PRIMARY KEY do not limit date range.

3. Final Answer:

   CHECK (birthdate < CURRENT_DATE) -> Option C

4. Quick Check:

   CHECK constraints enforce value rules [OK]

Hint: Use CHECK with date comparison for valid date ranges [OK]

Common Mistakes:

• Using NOT NULL to enforce date range
• Confusing UNIQUE or PRIMARY KEY with value limits
• Not using CURRENT_DATE in CHECK