Modifying Tables in SQL using ALTER

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);