Modifying Tables in SQL using ALTER

Source: Dev.to

Making a Column NOT NULL

ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;

Adding a Unique Constraint

ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);

Adding a Check Constraint

ALTER TABLE products
ADD CONSTRAINT check_price CHECK (price > 0);

Setting a Default Value

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';

Adding a Column with Constraints

ALTER TABLE employees
ADD COLUMN salary DECIMAL(10,2) NOT NULL CHECK (salary > 10000);

Managing Foreign Keys with ON DELETE CASCADE

-- Remove the existing foreign key constraint
ALTER TABLE employees
DROP CONSTRAINT employees_department_id_fkey;

-- Add a new foreign key with cascade delete
ALTER TABLE employees
ADD CONSTRAINT employees_department_id_fkey
FOREIGN KEY (department_id)
REFERENCES departments(id)
ON DELETE CASCADE;

Dropping a Check Constraint

ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;

Viewing Table Definition

\d accounts;

Adding a Composite Unique Constraint

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