1. Making a Column NOT NULL
Suppose we already created a customers table, but now we want to ensure that email is always provided.
ALTER TABLE customers ALTER COLUMN email SET NOT NULL;
2. Adding UNIQUE Constraint
To make sure no two users have the same username:
ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);
3. Adding CHECK Constraint
We want to ensure product price is always greater than 0:
ALTER TABLE products
ADD CONSTRAINT check_price CHECK (price > 0);
4. Setting DEFAULT Value
To make status = 'pending' by default:
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';
5. Adding a Column with Constraints
Now we add a salary column to employees:
ALTER TABLE employees
ADD COLUMN salary DECIMAL(10,2) NOT NULL CHECK (salary > 10000);
6. Modifying Foreign Key with CASCADE
To automatically delete employees when a department is deleted:
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;
7. Dropping a CHECK Constraint
To remove the balance restriction:
ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;
d accounts;
8. Adding Composite UNIQUE Constraint
To ensure a combination is unique:
ALTER TABLE payments
ADD CONSTRAINT unique_user_transaction
UNIQUE (user_id, transaction_id);