CHECK constraint in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to add a CHECK constraint that ensures age is at least 18.

SQL

CREATE TABLE users (id INT, age INT [1] chk_age CHECK (age >= 18));

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

ADEFAULT

BPRIMARY KEY

CCHECK

DCONSTRAINT

Attempts:

3 left

💡 Hint

Common Mistakes

Using DEFAULT instead of CONSTRAINT

Using PRIMARY KEY where CHECK is needed

Omitting CONSTRAINT keyword

The CONSTRAINT keyword is used to name or define a constraint such as CHECK in a table definition.

2fill in blank

medium

Complete the code to add a CHECK constraint that ensures salary is positive.

SQL

ALTER TABLE employees ADD [1] chk_salary CHECK (salary > 0);

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

ACONSTRAINT

BINDEX

CDEFAULT

DPRIMARY KEY

Attempts:

3 left

💡 Hint

Common Mistakes

Using INDEX instead of CONSTRAINT

Using DEFAULT keyword incorrectly

Omitting CONSTRAINT keyword

The CONSTRAINT keyword is used to add a CHECK constraint to an existing table.

3fill in blank

hard

Fix the error in the CHECK constraint that should ensure quantity is non-negative.

SQL

CREATE TABLE orders (id INT, quantity INT [1] CHECK (quantity >= 0));

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

ACHECK

BCONSTRAINT

CDEFAULT

DPRIMARY KEY

Attempts:

3 left

💡 Hint

Common Mistakes

Missing parentheses around the condition

Using CONSTRAINT without CHECK keyword

Using DEFAULT instead of CHECK

The CHECK keyword must be followed by parentheses enclosing the condition. The code is missing parentheses around quantity >= 0.

4fill in blank

hard

Fill both blanks to create a CHECK constraint that ensures rating is between 1 and 5 inclusive.

SQL

CREATE TABLE reviews (id INT, rating INT [1] chk_rating CHECK (rating [2] 1 AND rating <= 5));

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

ACONSTRAINT

B>=

C<=

DDEFAULT

Attempts:

3 left

💡 Hint

Common Mistakes

Using DEFAULT instead of CONSTRAINT

Using < instead of >= for the lower bound

Omitting CONSTRAINT keyword

Use CONSTRAINT to define the constraint and >= to check rating is at least 1.

5fill in blank

hard

Fill all three blanks to add a named CHECK constraint that ensures price is positive and less than 1000.

SQL

ALTER TABLE products ADD [1] price_check [2] CHECK (price [3] 0 AND price < 1000);

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

ACONSTRAINT

B>

CCHECK

DDEFAULT

Attempts:

3 left

💡 Hint

Common Mistakes

Using DEFAULT instead of CONSTRAINT

Using >= instead of > for price positivity

Omitting CHECK keyword

Use CONSTRAINT to name the constraint, CHECK to define it, and > to ensure price is positive.

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