Alter Tables

ALTER TABLE constraints

QN:1

You have a table customers with a column email that currently allows NULL values. Modify the table so that future entries must always have an email.

Query:
ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;

email should not be empty for future records we use SET NOT NULL

QN:2

In the users table, ensure that the username column is unique across all records using an ALTER statement.

Query:
ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);

username should not repeat we add UNIQUE constraint

QN:3

In the products table, enforce that price must always be greater than 0 using an ALTER command.

Query:
ALTER TABLE products
ADD CONSTRAINT check_price CHECK (price > 0);

price should always be greater than 0 we use CHECK constraint

QN:4

Modify the orders table so that the status column defaults to 'pending' if no value is provided during insertion.

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

if no value is given status should automatically become pending

QN:5

Alter the employees table by adding a new column salary such that
It cannot be NULL
It must always be greater than 10,000

Query:
ALTER TABLE employees
ADD COLUMN salary INT NOT NULL CHECK (salary > 10000);

salary should not be empty and must be above 10000 we use NOT NULL and CHECK

QN:6

Modify the foreign key constraint between employees and departments so that when a department is deleted, all related employees are automatically removed.

Query:
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;

existing foreign key cannot be modified directly we drop and recreate with CASCADE

QN:7

In the accounts table, remove an existing CHECK constraint that enforces balance >= 0.

Query:
ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;

we want to remove condition on balance we drop CHECK constraint

QN:8

In the payments table, ensure that the combination of user_id and transaction_id is unique using an ALTER TABLE statement.

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

same user should not have duplicate transaction_id we use combination UNIQUE