ALTER TABLE

Today I learned that creating a table is only the first step in SQL. Sometimes, after creating a table, we may realize that we need to change some rules. For example, maybe a column should not allow empty values, or maybe we want to add a new constraint later.
For this, SQL gives us an important command called ALTER TABLE.

ALTER TABLE is used when we want to change the structure of an existing table without deleting it and creating it again.

In this blog, I am going to explain different ALTER TABLE tasks in a simple way.

1. Making email compulsory in the customers table Suppose we already have a table called customers, and its email column currently allows NULL values. But now we want all future records to обязательно have an email value.

For that, we can modify the column using:

ALTER TABLE customers
ALTER COLUMN email SET NOT NULL;
What this does:
This command makes the email field compulsory from now on.
So, while inserting new records, we cannot leave the email empty.

1. Making username unique in the users table In many applications, usernames should be unique. Two users should not have the same username. If the table is already created, we can add this rule later using ALTER TABLE.

ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);
What this does:
This adds a UNIQUE constraint on the username column.
That means duplicate usernames are not allowed.

1. Ensuring product price is always greater than 0 In the products table, price should never be zero or negative. This can be enforced using a CHECK constraint.

ALTER TABLE products
ADD CONSTRAINT check_price_positive CHECK (price > 0);
What this does:
This makes sure the price value is always greater than 0.
So invalid prices cannot be inserted.

1. Setting default status in orders table Sometimes when we insert a new order, we may forget to mention the order status. In such cases, it is useful to automatically set a default value like 'pending'.

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';
What this does:
If no status is given during insertion, SQL will automatically store 'pending'.

1. Adding a salary column in employees table Now let us say we want to add a new column called salary in the employees table. The condition is:

• salary should not be NULL
• salary should always be greater than 10,000 This can be done like this:

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

• This creates a new column called salary and applies two rules:
• it must have a value
• the value must be above 10,000
• Changing foreign key so deleting a department also deletes employees Suppose the employees table is connected to the departments table using a foreign key. Now we want this behavior:

If a department is deleted, all employees belonging to that department should also be deleted automatically.

Usually, first we remove the old foreign key constraint, then add a new one with ON DELETE CASCADE.

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;
What this does:
If a department row is deleted, all matching employee rows are also deleted automatically.

1. Removing an existing CHECK constraint from accounts table In the accounts table, assume there is already a CHECK constraint that ensures balance >= 0. If we want to remove that rule, we need to drop the constraint.

Example:

ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;
What this does:
This removes the check condition from the balance column.

1. Making combination of user_id and transaction_id unique in payments table In the payments table, one user_id and transaction_id combination should not repeat. This means the pair together must be unique.

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

What this does:
This prevents duplicate entries with the same user_id and transaction_id combination.