This exercise was about ALTER TABLE. Instead of creating table again, we modify existing table like adding constraints, defaults, columns etc.
- Make email NOT NULL in customers table
sql
id="h4r9b2"
ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;
This makes sure future rows must have email.
- Make username unique in users table
sql
id="9zv1qx"
ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);
This prevents duplicate usernames.
- Add check constraint price > 0 in products table
sql
id="g6p8la"
ALTER TABLE products
ADD CONSTRAINT price_check CHECK (price > 0);
Now price cannot be zero or negative.
- Set default status 'pending' in orders table
sql
id="s8k3po"
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';
If status not given, it will automatically become pending.
- Add salary column in employees table with conditions
sql
id="x2w7mn"
ALTER TABLE employees
ADD COLUMN salary INT NOT NULL;
ALTER TABLE employees
ADD CONSTRAINT salary_check CHECK (salary > 10000);
Salary cannot be null and must be > 10000.
- Modify foreign key so delete department deletes employees
sql
id="f5t9yr"
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;
Now deleting department deletes employees automatically.
- Remove check constraint balance >= 0 from accounts table
sql
id="k7d3qp"
ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;
This removes the check constraint.
- Make combination of user_id and transaction_id unique in payments table
sql
id="m1c8zs"
ALTER TABLE payments
ADD CONSTRAINT unique_payment UNIQUE (user_id, transaction_id);
This prevents duplicate transaction per user.
From this exercise I learned ALTER TABLE is used to modify table structure without deleting table. Very useful when database already has data.
Top comments (0)