CHECK constraint in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

CHECK constraint behavior on insert

Consider this table definition:

CREATE TABLE Employees (
  ID INT PRIMARY KEY,
  Age INT CHECK (Age >= 18)
);

What happens if you run this insert?

INSERT INTO Employees (ID, Age) VALUES (1, 16);

A The insert fails with a CHECK constraint violation error.

B The insert succeeds and the row is added.

C The insert succeeds but Age is set to NULL.

D The insert succeeds but Age is automatically set to 18.

💡 Hint

CHECK constraints prevent invalid data from being inserted.

The CHECK constraint enforces that Age must be 18 or older. Trying to insert 16 violates this rule, so the insert fails.

🧠 Conceptual

intermediate

Purpose of CHECK constraints

What is the main purpose of a CHECK constraint in a database table?

A To speed up queries by indexing columns.

B To create a relationship between two tables.

C To enforce a rule that limits the values allowed in a column.

D To automatically generate unique IDs for rows.

💡 Hint

Think about data validation rules inside a table.

CHECK constraints ensure that only values meeting a condition can be stored in a column, helping keep data valid.

📝 Syntax

advanced

Identify the correct CHECK constraint syntax

Which of the following SQL statements correctly adds a CHECK constraint to ensure salary is positive?

A ALTER TABLE Employees ADD CONSTRAINT chk_salary CHECK salary > 0;

B ALTER TABLE Employees ADD CONSTRAINT chk_salary salary > 0 CHECK;

C ALTER TABLE Employees ADD CHECK salary > 0;

D ALTER TABLE Employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

💡 Hint

CHECK conditions must be enclosed in parentheses.

The correct syntax requires the CHECK condition inside parentheses and the CONSTRAINT keyword before the name.

🔧 Debug

advanced

Why does this CHECK constraint fail to enforce the rule?

Given this table creation:

CREATE TABLE Products (
  ID INT PRIMARY KEY,
  Price DECIMAL(10,2),
  CHECK Price > 0
);

Why does the database allow inserting a product with Price = -5?

A The CHECK constraint syntax is incorrect; it needs parentheses around the condition.

B The database does not support CHECK constraints on DECIMAL columns.

C The Price column must be declared NOT NULL for the CHECK to work.

D The primary key constraint overrides the CHECK constraint.

💡 Hint

Look carefully at the CHECK syntax.

CHECK constraints require the condition to be enclosed in parentheses. Without them, the constraint is ignored or misinterpreted.

❓ optimization

expert

Optimizing CHECK constraints for performance

You have a large table with a CHECK constraint on a complex expression involving multiple columns. What is the best way to optimize performance when inserting many rows?

A Remove the CHECK constraint and rely on application code validation only.

B Temporarily disable the CHECK constraint during bulk inserts and re-enable it afterward.

C Add an index on the columns used in the CHECK constraint.

D Rewrite the CHECK constraint as a trigger to improve speed.

💡 Hint

Think about how constraints affect bulk operations.

Disabling the CHECK constraint during bulk inserts avoids repeated checks, improving speed. Re-enabling afterward ensures data integrity.