Rules the database enforces for you, and code that runs itself
SQL lets you attach complex rules directly to a table's definition, and triggers take this further — code that the DBMS runs automatically in response to a data change, without any application asking for it.
After this lesson
You should be able to
- Write a CHECK constraint that enforces a business rule beyond a simple type.
- Explain what a trigger is and name one situation where it is more appropriate than application code.
CHECK constraints: rules a data type alone cannot express
A column type like INTEGER only restricts the shape of a value, not its meaning — fee INTEGER accepts -5000 just as happily as 50000. A CHECK constraint adds a business rule the DBMS enforces on every insert and update — CHECK (fee >= 0) rejects a negative fee outright, at the database level, regardless of which application or query attempted the write.
This matters because application-level validation alone is fragile — if even one application, script, or manual query bypasses that check, bad data enters the table. A CHECK constraint at the schema level is the one place that cannot be bypassed, similar to how a foreign key from Unit 2 cannot be bypassed by any application either.
CREATE TABLE Student (
roll_number INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
fee INT CHECK (fee >= 0),
branch VARCHAR(10) CHECK (branch IN ('CSE','ECE','MECH'))
);A trigger is a function that fires by itself
A trigger is a piece of code the DBMS runs automatically when a specified event happens on a table — before or after an INSERT, UPDATE, or DELETE. Unlike application code, a trigger runs no matter which application or query caused the change, because it lives inside the database itself rather than inside any one program.
A classic use is maintaining a derived value automatically: a trigger on Borrows that fires AFTER INSERT could automatically decrease a Book's available_copies count, so every application that inserts a borrow record gets this side effect for free, instead of every application programmer having to remember to write that decrement themselves. This is the same discipline as the DBMS-level enforcement CHECK constraints provide, extended from validating data to actively reacting to it.
Try it yourself
Write a CHECK constraint ensuring a Book's available_copies never exceeds its total_copies, using Book(isbn, title, total_copies, available_copies).
Need a hint?
A CHECK constraint can compare two columns of the same row directly, not just a column against a fixed value.
Check the worked solution
CHECK (available_copies <= total_copies), added inside the CREATE TABLE statement. This directly compares two columns of the same row and is enforced on every insert and update, so no application can accidentally set available_copies higher than total_copies.
Quick check
Why is a CHECK constraint more reliable than validating the same rule only inside application code?
Why this lesson exists
Syllabus mapping
NULL values, Complex Integrity Constraints in SQL, Triggers
Maps to course outcomes CO1, CO2.