CA 40 – Alter Tables

Source: Dev.to

Make email NOT NULL in customers table

ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;

Ensures that future rows must have an email value.

Make username unique in users table

ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);

Prevents duplicate usernames.

Add check constraint price > 0 in products table

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

Now the price cannot be zero or negative.

Set default status to 'pending' in orders table

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

If status is not provided, it will automatically be set to 'pending'.

Add salary column in employees table with conditions

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 greater than 10,000.

Modify foreign key so deleting a department deletes its 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;

Now deleting a department automatically deletes its associated employees.

Remove check constraint balance >= 0 from accounts table

ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;

Removes the check constraint on the balance column.

Make combination of user_id and transaction_id unique in payments table

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

Prevents duplicate transactions for the same user.

From this exercise I learned that ALTER TABLE is used to modify a table’s structure without deleting the table, which is very useful when the database already contains data.