Day 8: Constraints and Triggers

In Day 8 of our PostgreSQL learning series, we’ll delve into constraints and triggers in PostgreSQL. Constraints are rules defined on columns or tables to enforce data integrity, while triggers are special stored procedures that automatically execute in response to specified database events. We’ll cover both concepts in detail with explanations, commands, and examples.

Constraints

Constraints in PostgreSQL help ensure data integrity by enforcing rules on the values stored in columns or rows. Common types of constraints include:

1. NOT NULL: Ensures that a column cannot contain NULL values.
2. UNIQUE: Ensures that all values in a column (or a set of columns) are unique.
3. PRIMARY KEY: Combines the NOT NULL and UNIQUE constraints to uniquely identify each row in a table.
4. FOREIGN KEY: Establishes a relationship between two tables, enforcing referential integrity.
5. CHECK: Defines a condition that each row must satisfy.

Example:

Let’s create a table with various constraints:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department_id INT,
    CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments (department_id),
    CONSTRAINT chk_salary CHECK (salary >= 0)
);

In this example:

• employee_id is the primary key, ensuring each employee has a unique identifier.
• first_name and last_name columns cannot contain NULL values.
• email column must contain unique values.
• department_id column references the department_id column in the departments table.
• salary column must be greater than or equal to 0.

Here are examples of each trigger event and constraints with commands:

Constraints:

1. NOT NULL Constraint:

CREATE TABLE example (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

In this example, the name column cannot contain NULL values.

2. UNIQUE Constraint:

CREATE TABLE example (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

In this example, the email column must contain unique values.

3. PRIMARY KEY Constraint:

CREATE TABLE example (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

In this example, the id column serves as the primary key, ensuring each row has a unique identifier.

4. FOREIGN KEY Constraint:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

In this example, the department_id column references the id column in the departments table, establishing a foreign key relationship.

5. CHECK Constraint:

CREATE TABLE example (
    id SERIAL PRIMARY KEY,
    age INT CHECK (age >= 18)
);

In this example, the age column must be greater than or equal to 18.

Triggers

Triggers are special stored procedures that automatically execute in response to specific database events, such as INSERT, UPDATE, or DELETE operations on a table. Triggers can be defined to enforce data integrity, audit changes, or perform complex business logic.

Example:

Let’s create a trigger that automatically updates the last_updated column whenever a row in the employees table is updated:

CREATE OR REPLACE FUNCTION update_last_updated()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_updated = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_last_updated
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_last_updated();

In this example:

• We define a function update_last_updated() that sets the last_updated column to the current timestamp (NOW()).
• We create a trigger trg_update_last_updated that fires before an update operation on the employees table, executing the update_last_updated() function for each affected row.

Triggers:

1. BEFORE INSERT Trigger:

CREATE OR REPLACE FUNCTION before_insert_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- Trigger logic here
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert_trigger
BEFORE INSERT ON example
FOR EACH ROW
EXECUTE FUNCTION before_insert_trigger_function();

In this example, the before_insert_trigger_function() function is executed before each INSERT operation on the example table.

2. AFTER INSERT Trigger:

CREATE OR REPLACE FUNCTION after_insert_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- Trigger logic here
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_insert_trigger
AFTER INSERT ON example
FOR EACH ROW
EXECUTE FUNCTION after_insert_trigger_function();

In this example, the after_insert_trigger_function() function is executed after each INSERT operation on the example table.

3. BEFORE UPDATE Trigger:

CREATE OR REPLACE FUNCTION before_update_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- Trigger logic here
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_update_trigger
BEFORE UPDATE ON example
FOR EACH ROW
EXECUTE FUNCTION before_update_trigger_function();

In this example, the before_update_trigger_function() function is executed before each UPDATE operation on the example table.

4. AFTER UPDATE Trigger:

CREATE OR REPLACE FUNCTION after_update_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- Trigger logic here
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_update_trigger
AFTER UPDATE ON example
FOR EACH ROW
EXECUTE FUNCTION after_update_trigger_function();

In this example, the after_update_trigger_function() function is executed after each UPDATE operation on the example table.

5. BEFORE DELETE Trigger:

CREATE OR REPLACE FUNCTION before_delete_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- Trigger logic here
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_delete_trigger
BEFORE DELETE ON example
FOR EACH ROW
EXECUTE FUNCTION before_delete_trigger_function();

In this example, the before_delete_trigger_function() function is executed before each DELETE operation on the example table.

6. AFTER DELETE Trigger:

CREATE OR REPLACE FUNCTION after_delete_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- Trigger logic here
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_delete_trigger
AFTER DELETE ON example
FOR EACH ROW
EXECUTE FUNCTION after_delete_trigger_function();

In this example, the after_delete_trigger_function() function is executed after each DELETE operation on the example table.

These examples illustrate the usage of constraints and triggers in PostgreSQL, allowing you to maintain data integrity and automate database actions effectively.

Summary:

• Constraints enforce rules on columns or tables to maintain data integrity.
• Common constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK.
• Triggers are special stored procedures that automatically execute in response to specified database events.
• Triggers can be used to enforce data integrity, audit changes, or perform complex business logic.

Understanding constraints and triggers is essential for designing robust and secure database schemas in PostgreSQL. Tomorrow, we’ll explore views and materialized views for simplified data access and management. Stay tuned for more PostgreSQL learning!