Alter Queries

In this assignment, I worked on modifying existing tables using ALTER TABLE. This helped me understand how to update constraints without recreating tables.

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

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