CHECK constraint in SQL - Time & Space Complexity

Time Complexity: CHECK constraint

O(n)

Understanding Time Complexity

When we use a CHECK constraint in SQL, the database checks if data meets certain rules before saving it.

We want to know how the time to check data grows as we add more rows.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

CREATE TABLE Employees (
  ID INT PRIMARY KEY,
  Age INT,
  Salary DECIMAL(10,2),
  CHECK (Age >= 18 AND Salary >= 0)
);

INSERT INTO Employees (ID, Age, Salary) VALUES (1, 25, 50000);
INSERT INTO Employees (ID, Age, Salary) VALUES (2, 17, 40000); -- This will fail

This code creates a table with a CHECK constraint to ensure Age is at least 18 and Salary is not negative.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: The database checks the constraint for each new row inserted or updated.
• How many times: Once per row operation, so the check runs for every row added or changed.

How Execution Grows With Input

Each time we add a row, the database runs the CHECK condition once.

Input Size (n)	Approx. Operations
10	10 checks
100	100 checks
1000	1000 checks

Pattern observation: The number of checks grows directly with the number of rows inserted or updated.

Final Time Complexity

Time Complexity: O(n)

This means the time to check constraints grows in a straight line as you add more rows.

Common Mistake

[X] Wrong: "The CHECK constraint runs once for the whole table regardless of rows."

[OK] Correct: The database checks each row individually when it is inserted or updated, so the time grows with the number of rows.

Interview Connect

Understanding how constraints affect performance helps you design databases that keep data correct without slowing down too much.

Self-Check

"What if we added a CHECK constraint that involves a subquery? How would the time complexity change?"