ALTER TABLE

Source: Dev.to

Making email compulsory in the customers table

Suppose we already have a table called customers, and its email column currently allows NULL values. We now want all future records to обязательно have an email value.
For that, we can modify the column using:

ALTER TABLE customers

Making username unique in the users table

In many applications, usernames should be unique—two users should not share the same username. If the table is already created, we can add this rule later using:

ALTER TABLE users

Ensuring product price is always greater than 0

In the products table, the price should never be zero or negative. This can be enforced with a CHECK constraint:

ALTER TABLE products

Setting a default status in the orders table

When inserting a new order, the order status might be omitted. To automatically set a default value such as 'pending', use:

ALTER TABLE orders

Adding a salary column in the employees table

We want to add a new column called salary with the following conditions:

• salary should not be NULL.
• salary should always be greater than 10,000.

ALTER TABLE employees

This creates the new column and applies the two rules: a required value and a minimum of 10,000.

Changing a foreign key so deleting a department also deletes employees

The employees table is linked to the departments table via a foreign key. To make deletions cascade:

1. Remove the existing foreign‑key constraint.
2. Add a new one with ON DELETE CASCADE.

ALTER TABLE employees

Removing an existing CHECK constraint from the accounts table

Assume the accounts table has a CHECK constraint that ensures balance >= 0. To drop this rule:

ALTER TABLE accounts

Making the combination of user_id and transaction_id unique in the payments table

In the payments table, the pair (user_id, transaction_id) should not repeat. To enforce uniqueness of this combination:

ALTER TABLE payments

This prevents duplicate entries with the same user_id and transaction_id combination.