DEV Community

Mohammed Azim J
Mohammed Azim J

Posted on

CA 40 – Alter Tables

This exercise was about ALTER TABLE. Instead of creating table again, we modify existing table like adding constraints, defaults, columns etc.

  1. 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.

  1. Make username unique in users table

sql
id="9zv1qx"
ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);

This prevents duplicate usernames.

  1. 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.

  1. 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.

  1. 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.

  1. 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.

  1. Remove check constraint balance >= 0 from accounts table

sql
id="k7d3qp"
ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;

This removes the check constraint.

  1. 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)