ASSIGNMENT 40

1. Enforcing NOT NULL on email in customers table

Introduction

Sometimes, a column initially allows NULL values, but later business rules require it to always have data. Here, we enforce that every customer must have an email.

Code

ALTER TABLE customers MODIFY email VARCHAR(255) NOT NULL;

Explanation

MODIFY changes the definition of the column.
NOT NULL ensures future records must include an email.
Existing NULL values must be handled before applying this (or it will fail).

1. Making username UNIQUE in users table

Introduction

Usernames should be unique to avoid duplication and conflicts.

Code

ALTER TABLE users ADD CONSTRAINT unique_username UNIQUE (username);

Explanation

Adds a UNIQUE constraint.
Prevents duplicate usernames.
Automatically creates an index for faster lookups.

1. Enforcing price > 0 in products table

Introduction

Products should never have zero or negative prices.

Code

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

Explanation

CHECK constraint validates data before insertion/update.
Ensures all product prices are strictly positive.

1. Setting default status as 'pending' in orders

Introduction

If no status is provided, the system should automatically assign a default value.

Code
ALTER TABLE orders MODIFY status VARCHAR(50) DEFAULT 'pending';

Explanation

DEFAULT 'pending' assigns a value automatically.
Simplifies insert operations and ensures consistency.

1. Adding salary column with constraints in employees

Introduction

We add a new column with rules to ensure valid salary values.

Code

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

Explanation

Adds a new column salary.
NOT NULL → salary is mandatory.
CHECK → ensures salary is greater than 10,000.

1. Cascading delete between departments and employees

Introduction

When a department is deleted, all related employees should also be removed automatically.

Code
ALTER TABLE employees DROP FOREIGN KEY fk_department;

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

Explanation

First removes the existing foreign key.
Recreates it with ON DELETE CASCADE.
Ensures dependent rows are automatically deleted.

1. Removing CHECK constraint from accounts table

Introduction

Sometimes constraints are no longer needed and must be removed.

Code

ALTER TABLE accounts DROP CHECK check_balance;

Explanation

Removes the constraint enforcing balance >= 0.
Constraint name (check_balance) must be known beforehand.

1. Making (user_id, transaction_id) unique in payments

Introduction

Some scenarios require composite uniqueness, where a combination of columns must be unique.

Code

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

Explanation

Ensures no duplicate pair of user_id and transaction_id.
Useful for preventing duplicate transactions per user.

Conclusion

Using ALTER TABLE, we can:

Enforce data integrity rules
Modify existing schema without recreating tables
Improve data quality and reliability