CHECK constraint in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a CHECK constraint in SQL?

A CHECK constraint is a rule that limits the values allowed in a column. It ensures data meets specific conditions before being saved.

beginner

How does a CHECK constraint help maintain data quality?

It prevents invalid data by allowing only values that satisfy the condition defined in the CHECK constraint.

beginner

Write a simple CHECK constraint to ensure age is 18 or older.

CHECK (age >= 18)

intermediate

Can a CHECK constraint use multiple conditions? Give an example.

Yes, using AND or OR. Example: CHECK (age >= 18 AND age <= 65) ensures age is between 18 and 65.

beginner

What happens if you try to insert data that violates a CHECK constraint?

The database rejects the insert or update and shows an error, preventing invalid data from being saved.

What does a CHECK constraint do in a database?

A Creates a backup of the database

B Limits values in a column based on a condition

C Deletes duplicate rows automatically

D Encrypts data in a column

A CHECK constraint limits the values allowed in a column by enforcing a condition.

Which of the following is a valid CHECK constraint for a salary column to be positive?

A CHECK (salary > 0)

B CHECK salary > 0

C CHECK salary = positive

D CHECK (salary IS NOT NULL)

The correct syntax uses parentheses and a condition: CHECK (salary > 0).

Can a CHECK constraint use logical operators like AND or OR?

A Only AND is allowed, not OR

B No, only one condition is allowed

C Only OR is allowed, not AND

D Yes, to combine multiple conditions

CHECK constraints can combine multiple conditions using AND or OR.

What happens if data violates a CHECK constraint during insertion?

A The database automatically fixes the data

B The data is inserted anyway

C The insertion fails with an error

D The data is inserted but marked invalid

The database rejects the insert and shows an error to maintain data integrity.

Which SQL keyword is used to add a CHECK constraint when creating a table?

A CHECK

B LIMIT

C CONDITION

D RESTRICT

The CHECK keyword defines the condition for the constraint.

Explain what a CHECK constraint is and how it helps keep data correct.

Think about rules that data must follow before saving.

Write an example of a CHECK constraint that ensures a product price is greater than zero and less than 1000.

Use AND to combine two conditions inside CHECK.

Practice

1. What is the main purpose of a CHECK constraint in SQL?

easy

A. To automatically generate unique IDs

B. To create a backup of the table data

C. To speed up query performance

D. To enforce rules on column values to prevent invalid data

Solution

1. Step 1: Understand what a CHECK constraint does

   A CHECK constraint sets a rule that data must follow when inserted or updated in a table.

2. Step 2: Identify the purpose from the options

   Only To enforce rules on column values to prevent invalid data describes enforcing rules on data to prevent invalid entries.

3. Final Answer:

   To enforce rules on column values to prevent invalid data -> Option D

4. Quick Check:

   CHECK constraint = enforce data rules [OK]

Hint: CHECK constraints prevent bad data from entering tables [OK]

Common Mistakes:

• Confusing CHECK with indexing or keys
• Thinking CHECK creates backups
• Assuming CHECK improves speed

2. Which of the following is the correct syntax to add a CHECK constraint on a column age to allow only values greater than 18?

easy

A. ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age > 18);

B. ALTER TABLE users CHECK (age > 18);

C. ALTER TABLE users ADD CHECK age > 18;

D. ALTER TABLE users ADD CONSTRAINT chk_age CHECK age > 18;

Solution

1. Step 1: Recall correct syntax for adding CHECK constraint

   The correct syntax uses ADD CONSTRAINT with a name, then CHECK with parentheses around the condition.

2. Step 2: Compare options to syntax

   ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age > 18); matches the syntax: ADD CONSTRAINT name CHECK (condition). Options A and C miss the constraint name or parentheses; D misses parentheses.

3. Final Answer:

   ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age > 18); -> Option A

4. Quick Check:

   ADD CONSTRAINT name CHECK (condition) = ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age > 18); [OK]

Hint: Use ADD CONSTRAINT name CHECK (condition) syntax [OK]

Common Mistakes:

• Omitting parentheses around condition
• Not naming the constraint
• Writing condition outside CHECK()

3. Given the table creation:

CREATE TABLE products (
  id INT,
  price DECIMAL(5,2),
  CONSTRAINT chk_price CHECK (price >= 0)
);

What happens if you run:

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

medium

A. The row is inserted successfully

B. The price is automatically set to 0

C. The insert fails due to CHECK constraint violation

D. The insert causes a syntax error

Solution

1. Step 1: Understand the CHECK constraint on price

   The constraint requires price to be greater than or equal to 0.

2. Step 2: Analyze the insert statement

   The insert tries to add price = -10.00, which violates the CHECK condition.

3. Final Answer:

   The insert fails due to CHECK constraint violation -> Option C

4. Quick Check:

   price < 0 violates CHECK = insert fails [OK]

Hint: CHECK rejects rows violating the condition [OK]

Common Mistakes:

• Assuming negative values are accepted
• Thinking CHECK auto-corrects values
• Confusing CHECK violation with syntax error

4. You try to create a table with this statement:

CREATE TABLE employees (
  id INT,
  salary INT CHECK salary > 0
);

But get a syntax error. What is the likely fix?

medium

A. Add a semicolon after salary

B. Add parentheses around the CHECK condition: CHECK (salary > 0)

C. Change salary to VARCHAR

D. Remove the CHECK constraint entirely

Solution

1. Step 1: Identify syntax for inline CHECK constraints

   Inline CHECK constraints require the condition to be inside parentheses.

2. Step 2: Check the given statement

   The statement misses parentheses around salary > 0, causing syntax error.

3. Final Answer:

   Add parentheses around the CHECK condition: CHECK (salary > 0) -> Option B

4. Quick Check:

   Inline CHECK needs parentheses [OK]

Hint: Always use parentheses around CHECK conditions [OK]

Common Mistakes:

• Omitting parentheses in inline CHECK
• Changing data type unnecessarily
• Ignoring syntax error details

5. You want to ensure a discount column in orders table is between 0 and 50 inclusive. Which CHECK constraint correctly enforces this?

hard

A. CHECK (discount >= 0 AND discount <= 50)

B. CHECK (discount > 0 AND discount < 50)

C. CHECK (discount BETWEEN 1 AND 50)

D. CHECK (discount != 0 OR discount != 50)

Solution

1. Step 1: Understand the required range including boundaries

   The discount must be at least 0 and at most 50, so boundaries are included.

2. Step 2: Evaluate each option

   B: CHECK (discount > 0 AND discount < 50) excludes 0 and 50 (strict inequalities). A: CHECK (discount >= 0 AND discount <= 50) includes boundaries with >= and <=. C: CHECK (discount BETWEEN 1 AND 50) excludes 0 (>=1 <=50). D: CHECK (discount != 0 OR discount != 50) is logically incorrect.

3. Final Answer:

   CHECK (discount >= 0 AND discount <= 50) -> Option A

4. Quick Check:

   Inclusive range needs >= and <= [OK]

Hint: Use >= and <= for inclusive CHECK ranges [OK]

Common Mistakes:

• Using > and < excludes boundary values
• Using incorrect bounds with BETWEEN
• Logical errors with OR instead of AND