PostgreSQL Constraints
What are Constraints?
Constraints are rules applied to table columns that control what type of data can be stored. They act like guards at the database gate, ensuring that only valid and meaningful data gets in.
By using constraints, we can:
- Prevent invalid or duplicate entries
- Maintain accuracy, consistency, and reliability of data
- Protect relationships between tables
Column-Level vs Table-Level Constraints
Column-Level Constraint: Applies only to one column (e.g., age > 0).
Table-Level Constraint: Applies to multiple columns or the entire table (e.g., making sure a foreign key matches another table).
Even setting a data type (like DATE, INT) is itself a constraint, because it limits the type of values allowed.
Example: A column of type DATE won’t allow text like "hello", only valid dates.
Commonly Used Constraints in PostgreSQL
- NOT NULL
- Prevents a column from being left empty.
- Example: Every student must have a name.
- UNIQUE
- Ensures no two rows have the same value in that column.
- Example: Each user must have a unique email.
- PRIMARY KEY
- Uniquely identifies each record in a table.
- It’s basically NOT NULL + UNIQUE.
- Example: A student’s roll number or employee ID.
- FOREIGN KEY
- Creates a link between two tables.
- Ensures that a value in one table must already exist in another.
- Example: An order must belong to a valid customer.
- CHECK
- Adds a condition that values must follow.
- Example: Age must always be greater than 0.
- DEFAULT
- Provides a default value if none is supplied.
- Example: If a new account is created, status defaults to "Active".
Constraints = rules to keep data clean, consistent, and meaningful. They help the database behave like a well-disciplined classroom where no one can break the rules.