ALTER TABLE

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.

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

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

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

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

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

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

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

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

Answer:
ALTER TABLE employees ADD COLUMN salary INT;
ALTER TABLE

ALTER TABLE employees ALTER COLUMN salary SET NOT NULL;
ALTER TABLE

ALTER TABLE employees ADD CONSTRAINT salary_check CHECK (salary > 10000);
ALTER TABLE

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

Answer:

ALTER TABLE employees ADD CONSTRAINT employees_department_id_fkey FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE;
ALTER TABLE

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

Answer:
ALTER TABLE accounts DROP CONSTRAINT accounts_balance_check;
ALTER TABLE

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

Answer:

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