DEV Community

Lokeshwaran S
Lokeshwaran S

Posted on

Alter Tables in SQL - CA40

My Thinking and Approach

Introduction

In this task, I worked on modifying existing database tables using ALTER TABLE statements. Instead of creating new tables, the goal was to update the structure of already existing tables by adding constraints, modifying columns, and enforcing rules.

This helped me understand how databases can be controlled and maintained properly.

Problem Statement

  • Modify existing tables using ALTER statements
  • Apply constraints like NOT NULL, UNIQUE, CHECK
  • Set default values
  • Manage foreign key behavior

My Initial Thought

At first, I thought:

  • ALTER TABLE is only used to add columns
  • Changes might be complicated

But I realized:

  • It can modify constraints as well
  • It is very useful for maintaining data integrity

Key Observation

  • Constraints help in controlling data
  • ALTER TABLE allows updating structure without deleting data
  • Each modification must be carefully applied

Solutions

1. Make email NOT NULL in customers table

Code (SQL) 

ALTER TABLE customers
MODIFY email VARCHAR(255) NOT NULL;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • Ensures email cannot be empty in future records

2. Make username UNIQUE in users table

Code (SQL) 

ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • Prevents duplicate usernames

3. Ensure price is greater than 0 in products table

Code (SQL) 

ALTER TABLE products
ADD CONSTRAINT check_price CHECK (price > 0);
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • Ensures valid product pricing

4. Set default value for status in orders table

Code (SQL) 

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • Automatically assigns pending if no value is given

5. Add salary column with constraints in employees table

Code (SQL) 

ALTER TABLE employees
ADD salary INT NOT NULL CHECK (salary > 10000);
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • Adds salary column
  • Ensures it is not null
  • Ensures salary is greater than 10000

6. Modify foreign key with cascade delete

Code (SQL) 

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

Explanation:

  • Deletes employees automatically when department is deleted

7. Remove CHECK constraint from accounts table

Code (SQL) 

ALTER TABLE accounts
DROP CONSTRAINT check_balance;
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • Removes restriction on balance

8. Make user_id and transaction_id unique in payments table

Code (SQL) 

ALTER TABLE payments
ADD CONSTRAINT unique_payment UNIQUE (user_id, transaction_id);
Enter fullscreen mode Exit fullscreen mode

Explanation:

  • Ensures no duplicate transactions per user

Final Understanding

  • ALTER TABLE is powerful for modifying structure
  • Constraints help maintain clean and valid data
  • Proper use of constraints improves database reliability

Conclusion

This task helped me understand how to control and update database tables efficiently without recreating them. It also improved my understanding of constraints and their importance in real-world applications.

Top comments (0)