Constraints & defaults
Overview
Constraints enforce rules on data at the database level. `NOT NULL` prevents missing values, `CHECK` validates conditions (e.g. `price > 0`), and `DEFAULT` provides a fallback value if none is inserted. ```sql CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR NOT NULL, price NUMERIC CHECK (price > 0), status VARCHAR DEFAULT 'pending' ); ```
Application code can have bugs and allow invalid data. Database constraints act as the final, unbreakable line of defense for data integrity.
Where used: schema definitions, data ingestion validation
Why learn this
- You can guarantee data reliability regardless of which application connects to the database.
- You reduce the need for defensive programming in your backend code.
Query walkthrough
SELECT * FROM orders WHERE status = 'pending';Focus: The status column often has a DEFAULT 'pending' and a CHECK constraint limiting it to 'pending', 'paid', or 'cancelled'.
Common mistakes
- Adding constraints after bad data exists: If you try to add a `NOT NULL` constraint to a column that already has NULL values, the database will throw an error. You must clean the data first.
Glossary
- integrity
- The accuracy, completeness, and reliability of data over its entire lifecycle.
- fallback
- A default option or alternative plan that is used when the primary choice is missing or fails.
Recall questions
- What is the difference between a CHECK constraint and a DEFAULT value?
Understanding checks
What rows does this return?
id: 5, name: Emma
The `country` column does not have a NOT NULL constraint, allowing Emma's country to be missing.
What rows does this return?
No rows
While we can't see the DDL here, realistic schemas use a CHECK(price > 0) constraint to prevent negative or zero prices, and our dataset follows this.
Practice tasks
Filter Valid Data
Given this query, change it to only select employees who have a manager (filtering out those where manager_id is NULL) as an auditing step before adding a NOT NULL constraint.
Challenge
Find Orders by Status
Write a query that finds the `id` and `customer_id` of all orders that have the status 'cancelled'.
Continue learning
Previous: Primary, foreign & unique keys