Understanding ALTER TABLE with Constraints in SQL

_In this task, I worked on modifying existing tables using ALTER TABLE. This helped me understand how to update table structure after it has already been created. I learned how to add constraints, change column properties, and manage relationships between tables.
_

1. Modifying customers table

Query:

ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;

Explanation:

In this query, I modified the email column so that it cannot store NULL values. This ensures that every customer must have an email in future entries.

1. Adding UNIQUE constraint in users table

Query:

ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);

Explanation:

Here, I added a UNIQUE constraint to the username column. This ensures that no two users can have the same username, which helps avoid duplicate records.

1. Adding CHECK constraint in products table

Query:

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

Explanation:

In this query, I added a CHECK condition to make sure that the price is always greater than 0. This prevents invalid values like zero or negative prices.

1. Setting DEFAULT value in orders table

Query:

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

Explanation:

Here, I set a default value for the status column. If no value is provided while inserting data, it will automatically be set to 'pending'.

1. Adding salary column in employees table

Query:

ALTER TABLE employees
ADD COLUMN salary DECIMAL(10,2) NOT NULL CHECK (salary > 10000);

Explanation:

In this query, I added a new column called salary. It cannot be NULL and must always be greater than 10000. This ensures that all employees have a valid salary value.

1. Modifying foreign key with cascade

Query:

ALTER TABLE employees_dept
DROP CONSTRAINT employees_dept_department_id_fkey;

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

Explanation:

Here, I modified the foreign key constraint. When a department is deleted, all related employees will also be deleted automatically. This helps maintain consistency in the database.

1. Removing CHECK constraint in accounts table

Query:

ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;

Explanation:

In this query, I removed the CHECK constraint that was enforcing balance to be greater than or equal to 0. After removing it, the table will allow any value for balance.

1. Adding composite UNIQUE constraint in payments table

Query:

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

Explanation:

Here, I added a composite UNIQUE constraint on user_id and transaction_id. This ensures that the same transaction cannot be repeated for the same user.

Conclude with :

Through this task, I understood how ALTER TABLE helps in modifying existing tables. It allows adding, removing, and updating constraints based on new requirements.This made me realize that database design is not fixed and can be updated when needed, but changes should be done carefully to maintain data consistency.