PostgreSQL: constraints

In PostgreSQL, constraints are rules applied to columns or tables ti prevent invalid data from entering the database. They ensure the accuracy, reliability and integrity of your data.

Think of them as the “watchman” of your database. If a user tries to insert or update data that violates a constraint, PostgresSQL will reject the operation and throw an error.

1. Common Column-Level Constraints

These are applied to individual columns to define what kind of data they can hold.

• NOT NULL: Ensures that a column cannot have a NULL value. This is essential for fields like “Username” or “Email.”

• UNIQUE: Guarantees that all values in a column are different. For example, two users cannot have the same Employee ID.

• CHECK: Allows you to specify a boolean expression that data must satisfy.
  • Example: CHECK (price > 0) ensures no products have a negative cost.

• DEFAULT: Provides a fallback value if no value is specified during an insert.

2. Key Constraints (Table Integrity)

These define how rows are identified and how tables relate to one another.

Primary Key (PRIMARY KEY)

A Primary Key uniquely identifies each row in a table. It is essentially a combination of NOT NULL and UNIQUE. A table can have only one primary key.

Foreign Key (FOREIGN KEY)

A Foreign Key ensures “Referential Integrity.” It links a column in one table to a column in another (usually a Primary Key). This prevents “orphan” records.

• Example: An Orders table has a customer_id that must exist in the Customers table.

3. Exclusion Constraints

These are more advanced and used to ensure that if any two rows are compared on specified columns using specified operators, at least one of these operator comparisons will return false.

• Use case: Preventing overlapping date ranges in a booking system (e.g., you can’t book the same room for the same time twice).

Summary Table

Constraint	Function	Data Integrity Type
NOT NULL	Prevents empty values.	Column Integrity
UNIQUE	Prevents duplicate values.	Column Integrity
PRIMARY KEY	Uniquely identifies a row.	Entity Integrity
FOREIGN KEY	Links two tables together.	Referential Integrity
CHECK	Ensures values meet a specific condition.	Domain Integrity

Example

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,            -- Unique & Not Null
    first_name TEXT NOT NULL,             -- Cannot be empty
    email TEXT UNIQUE,                    -- No duplicate emails
    salary NUMERIC CHECK (salary > 0),    -- Must be positive
    department_id INTEGER REFERENCES departments(id) -- Foreign Key
);

Would you like me to show you how to add a constraint to an existing table using the ALTER TABLE command?