Alter Tables

Modifying TabModifying Tables Using ALTER in PostgreSQL

In real-world database systems, requirements often change after tables are created. Instead of recreating tables, we use the ALTER TABLE command to update structure, constraints, and relationships. Below are practical examples demonstrating how to modify existing tables effectively.

Making Email Mandatory in Customers Table

Initially, the email column may allow NULL values. To enforce that every new record must include an email:

ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;
Enforcing Unique Usernames

To ensure that no two users share the same username, we add a unique constraint:

ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);
Adding Price Validation in Products Table

We enforce that product prices must always be greater than zero:

ALTER TABLE products
ADD CONSTRAINT check_price_positive CHECK (price > 0);
Setting Default Status in Orders Table

To automatically assign a default value of 'pending' when no status is provided:

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';
Adding Salary Column with Constraints

We extend the employees table by adding a salary column with strict rules:

ALTER TABLE employees
ADD COLUMN salary INT NOT NULL CHECK (salary > 10000);
Updating Foreign Key with Cascade Delete

To ensure that deleting a department also removes all related employees, we modify the foreign key constraint:

ALTER TABLE employees
DROP CONSTRAINT employees_department_id_fkey;

ALTER TABLE employees ADD CONSTRAINT employees_department_id_fkey FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE;

Removing a CHECK Constraint from Accounts Table
If there is an existing constraint enforcing balance >= 0, it can be removed as follows:

ALTER TABLE accounts DROP CONSTRAINT accounts_balance_check;

To prevent duplicate transactions for the same user, we enforce a composite unique constraint:

ALTER TABLE payments ADD CONSTRAINT unique_user_transaction UNIQUE (user_id, transaction_id);
ALTER TABLE paymentsADD CONSTRAINT unique_user_transaction UNIQUE (user_id, transaction_id);