Why constraints matter in SQL - Visual Breakdown

Concept Flow - Why constraints matter

Start: Insert or Update Data

↓

Check Constraints

↓

Pass

↓

Data Saved

When data is added or changed, constraints check if it follows rules. If yes, data saves; if no, error stops it.

Execution Sample

SQL

CREATE TABLE Users (
  ID INT PRIMARY KEY,
  Email VARCHAR(100) UNIQUE NOT NULL
);

INSERT INTO Users VALUES (1, '[e-mail]');
INSERT INTO Users VALUES (2, NULL);

Create a table with rules: ID must be unique, Email must be unique and not empty. Then try adding data.

Execution Table

Step	Action	Data Attempted	Constraint Checked	Result
1	Create Table	Users with ID PK, Email UNIQUE NOT NULL	N/A	Table created
2	Insert	(1, '[e-mail]')	Primary Key, Unique, Not Null	Success: Data saved
3	Insert	(2, NULL)	Not Null on Email	Fail: NULL not allowed, insert rejected

💡 Insert rejected because Email is NULL but NOT NULL constraint requires a value

Variable Tracker

Variable	Start	After Step 2	After Step 3
Users Table Rows	Empty	(1, '[e-mail]')	(1, '[e-mail]')

Key Moments - 2 Insights

Why did the second insert fail even though the ID was unique?

Because the Email column has a NOT NULL constraint, and the second insert tried to put NULL there. See execution_table row 3.

What happens if we try to insert a duplicate ID?

The PRIMARY KEY constraint prevents duplicates, so the insert would fail with an error, similar to the UNIQUE constraint failure shown.

Visual Quiz - 3 Questions

Look at the execution_table, what constraint caused the failure at step 3?

A Not Null constraint

B Unique constraint

C Primary Key constraint

D Foreign Key constraint

💡 Hint

Check the 'Constraint Checked' and 'Result' columns in execution_table row 3

At which step was data successfully saved to the Users table?

A Step 1

B Step 2

C Step 3

D No data saved

💡 Hint

Look at the 'Result' column in execution_table for success messages

If the UNIQUE constraint on Email was removed, what would happen at step 3?

A Insert would succeed despite NULL Email

B Insert would fail due to Primary Key

C Insert would fail due to Not Null constraint

D Insert would fail due to Foreign Key

💡 Hint

NOT NULL constraint is independent of UNIQUE, see execution_table row 3

Concept Snapshot

Constraints are rules on table columns.
They prevent bad or duplicate data.
Common constraints: PRIMARY KEY, UNIQUE, NOT NULL.
If data breaks rules, database rejects it.
This keeps data clean and reliable.

Full Transcript

When you add or change data in a database table, constraints check if the data follows rules. For example, a PRIMARY KEY must be unique, UNIQUE columns cannot repeat values, and NOT NULL columns must have a value. If data breaks these rules, the database stops the change and shows an error. This helps keep the data accurate and trustworthy. In the example, inserting a row with a NULL Email failed because the Email column has a NOT NULL constraint. This shows why constraints matter: they protect your data from mistakes or duplicates.

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