Why constraints matter in SQL - Quick Recap

Recall & Review

beginner

What is a constraint in a database?

A constraint is a rule applied to a database table to ensure data integrity and correctness. It restricts the type of data that can be inserted or updated.

beginner

Why do constraints matter in databases?

Constraints help keep data accurate, consistent, and reliable by preventing invalid or duplicate data from being stored.

beginner

Name two common types of constraints in SQL.

Two common constraints are PRIMARY KEY, which uniquely identifies each row, and NOT NULL, which ensures a column cannot have null values.

intermediate

How do constraints help when multiple people use the same database?

Constraints prevent users from entering wrong or conflicting data, so everyone sees accurate and consistent information.

beginner

What might happen if a database has no constraints?

Without constraints, the database could have duplicate, missing, or incorrect data, making it unreliable and hard to trust.

What does a PRIMARY KEY constraint do?

A Prevents any data from being inserted

B Ensures each row is unique and not null

C Allows duplicate values in a column

D Automatically deletes rows

A PRIMARY KEY uniquely identifies each row and cannot be null.

Which constraint prevents a column from having empty values?

A FOREIGN KEY

B CHECK

C UNIQUE

D NOT NULL

NOT NULL ensures that a column must have a value and cannot be left empty.

Why are constraints important when many users access the same database?

A They prevent invalid or conflicting data

B They make the database bigger

C They allow users to delete data easily

D They slow down the database

Constraints keep data accurate and consistent even with multiple users.

What could happen if a database has no constraints?

A The database will reject all data

B Data will always be correct

C Data might be incorrect or duplicated

D The database will run faster

Without constraints, invalid or duplicate data can be stored.

Which constraint ensures values in a column are unique but can be null?

A UNIQUE

B PRIMARY KEY

C NOT NULL

D CHECK

UNIQUE constraint allows unique values and can accept nulls.

Explain why constraints are important for maintaining data quality in a database.

Think about what happens if wrong data is allowed.

Describe how constraints help when multiple users work with the same database.

Consider how rules keep data trustworthy for everyone.

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