ALTER

To ensure that every customer must provide an email in the future, we need to prevent NULL values in the email column. This enforces data completeness and avoids issues like missing contact information.

ALTER TABLE customers ALTER COLUMN email SET NOT NULL;

This change guarantees that all future inserts must include an email value.

Usernames are typically used for login or identification, so they must be unique. To enforce this at the database level, we add a unique constraint.

ALTER TABLE users ADD CONSTRAINT unique_username UNIQUE (username);

This prevents duplicate usernames and ensures consistency across the system.

In a products table, price should never be zero or negative. To enforce this rule, we add a check constraint.

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

This ensures that only valid product prices are stored.

For an orders table, it is common to assign a default status when none is provided. This avoids NULL values and keeps workflows predictable.

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

Now, if no status is specified during insertion, it will automatically be set to 'pending'.

When adding a salary column to the employees table, we must ensure two things: it cannot be NULL and it must be greater than 10,000. This requires both a column addition and constraints.

ALTER TABLE employees ADD COLUMN salary INT NOT NULL CHECK (salary > 10000);

This enforces both presence and validity of salary values.

ALTER TABLE employees DROP CONSTRAINT employees_department_id_fkey;
ALTER TABLE employeesADD CONSTRAINT employees_department_id_fkey FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE;

This ensures referential integrity by automatically cleaning up dependent records.

Sometimes business rules change, and constraints need to be removed. For example, if the accounts table no longer requires balance to be non-negative, we can drop the check constraint.

ALTER TABLE accounts DROP CONSTRAINT accounts_balance_check;

This removes the restriction and allows more flexible balance values.

To ensure that each payment is uniquely identified per user, we enforce a composite unique constraint on user_id and transaction_id.

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

This prevents duplicate transactions for the same user.