Alter Queries

Source: Dev.to

Tasks

1. Make email NOT NULL in customers

ALTER TABLE customers ALTER COLUMN email SET NOT NULL;

2. Make username UNIQUE in users

ALTER TABLE users ADD CONSTRAINT unique_username UNIQUE (username);

3. Add CHECK constraint on price > 0 in products

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

4. Set default 'pending' for status in orders

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

5. Add salary column with constraints in employees

ALTER TABLE employees ADD COLUMN salary INT NOT NULL CHECK (salary > 10000);

6. Modify foreign key with CASCADE in employees

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. Remove CHECK constraint on balance in accounts

ALTER TABLE accounts DROP CONSTRAINT accounts_balance_check;

8. Add composite UNIQUE in payments

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

What I Learned

• ALTER TABLE helps modify structure without deleting data.
• Constraints can be added, removed, or updated anytime.
• Naming constraints properly makes them easier to manage.