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 TABLEhelps modify structure without deleting data - Constraints can be added, removed, or updated anytime
- Naming constraints properly makes them easier to manage
