arvb.net

SQL Alter

3 min read

The ALTER TABLE statement in SQL is used to modify the structure of an existing table. This includes adding, modifying, or dropping columns; adding or dropping constraints; renaming objects; and even adjusting column-level constraints. It’s a powerful tool for evolving a database schema over time.

Common ALTER Operations

1. Adding a Column

You can add new columns to a table without affecting existing data.

ALTER TABLE employees
ADD COLUMN middle_name VARCHAR(50);

Explanation:

  • Adds a new column middle_name of type VARCHAR(50) to the employees table.

2. Dropping a Column

Remove an existing column from a table.

ALTER TABLE employees
DROP COLUMN middle_name;

Explanation:

  • Removes the middle_name column from the employees table.
  • Caution: Dropping a column is irreversible and may result in data loss.

3. Adding a Constraint

You can add constraints to enforce data integrity.

Example: Adding a Check Constraint

ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary > 0);

Explanation:

  • Adds a check constraint named chk_salary to ensure that the salary column only contains values greater than 0.

Example: Adding a Foreign Key Constraint

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE;

Explanation:

  • Adds a foreign key constraint fk_customer to the orders table.
  • Ensures customer_id in orders references customer_id in customers.
  • Uses ON DELETE CASCADE to automatically delete orders when the corresponding customer is removed.

4. Dropping a Constraint

Remove an existing constraint from a table.

ALTER TABLE employees
DROP CONSTRAINT chk_salary;

Explanation:

  • Drops the check constraint chk_salary from the employees table.

5. Renaming a Column or Table

Renaming a Column:

ALTER TABLE employees
RENAME COLUMN fullname TO full_name;

Explanation:

  • Renames the fullname column to full_name in the employees table.

Renaming a Table:

ALTER TABLE employees
RENAME TO staff;

Explanation:

  • Renames the employees table to staff.

6. Setting or Removing Constraints on a Specific Column

SQL allows you to modify constraints that are applied directly to a column using the ALTER COLUMN clause.

a. Setting a Constraint

Setting a NOT NULL Constraint:
To ensure a column does not accept NULL values:

ALTER TABLE employees
ALTER COLUMN email SET NOT NULL;

Explanation:

  • Modifies the email column in the employees table so that it cannot store NULL values.

Setting a DEFAULT Value:
To provide a default value when none is specified:

ALTER TABLE employees
ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;

Explanation:

  • Sets the default value of created_at to the current timestamp if no value is provided during insertion.

b. Removing a Constraint

Dropping a NOT NULL Constraint:
To allow NULL values in a column:

ALTER TABLE employees
ALTER COLUMN email DROP NOT NULL;

Explanation:

  • Removes the NOT NULL restriction from the email column, permitting NULL entries.

Removing a DEFAULT Value:
To remove a default value from a column:

ALTER TABLE employees
ALTER COLUMN created_at DROP DEFAULT;

Explanation:

  • Drops the default setting on the created_at column.

Note: For constraints such as CHECK constraints that are defined inline, you typically must drop them using the constraint’s name.

Final Notes

  • Always backup your data or test schema changes in a development environment before applying them to production.
  • Review dependencies: When dropping columns or constraints, ensure that no dependent objects (e.g., views, triggers) are adversely affected.
  • Documentation: Keep track of schema changes for future reference and audits.

Graph View

Backlinks