Why constraints matter in SQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a table with a primary key constraint.

SQL

CREATE TABLE employees (id INT [1], name VARCHAR(100));

Drag options to blanks, or click blank then click option'

ACHECK

BFOREIGN KEY

CUNIQUE

DPRIMARY KEY

Attempts:

3 left

💡 Hint

Common Mistakes

Using FOREIGN KEY instead of PRIMARY KEY

Forgetting to add any constraint

The PRIMARY KEY constraint uniquely identifies each record in the table.

2fill in blank

medium

Complete the code to add a NOT NULL constraint to the email column.

SQL

CREATE TABLE users (user_id INT PRIMARY KEY, email VARCHAR(255) [1]);

Drag options to blanks, or click blank then click option'

AUNIQUE

BCHECK

CNOT NULL

DDEFAULT

Attempts:

3 left

💡 Hint

Common Mistakes

Using UNIQUE instead of NOT NULL

Leaving the column without any constraint

The NOT NULL constraint ensures that the email column cannot have empty values.

3fill in blank

hard

Fix the error in the constraint syntax to ensure age is always 18 or older.

SQL

CREATE TABLE members (member_id INT PRIMARY KEY, age INT CHECK (age [1] 18));

Drag options to blanks, or click blank then click option'

A>=

B<

C=

D!=

Attempts:

3 left

💡 Hint

Common Mistakes

Using less than operator which allows younger ages

Using equality operator which only allows exactly 18

The CHECK constraint with >= 18 ensures age is 18 or older.

4fill in blank

hard

Fill both blanks to create a foreign key constraint linking orders to customers.

SQL

CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, CONSTRAINT fk_customer FOREIGN KEY ([1]) REFERENCES [2](id));

Drag options to blanks, or click blank then click option'

Acustomer_id

Borders

Ccustomers

Dorder_id

Attempts:

3 left

💡 Hint

Common Mistakes

Referencing the wrong table

Using the wrong column name for the foreign key

The foreign key customer_id in orders references the id in customers table.

5fill in blank

hard

Fill all three blanks to create a table with a unique constraint and a default value.

SQL

CREATE TABLE products (product_id INT PRIMARY KEY, sku VARCHAR(50) [1], stock INT [2] 0, CONSTRAINT unique_sku [3] (sku));

Drag options to blanks, or click blank then click option'

AUNIQUE

BDEFAULT

DCHECK

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing UNIQUE and CHECK constraints

Forgetting to set a default value for stock

The sku column has a UNIQUE constraint to avoid duplicates, stock has a DEFAULT value of 0, and the constraint named unique_sku enforces uniqueness on sku.

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