sql-alter-table-command

SQL - ALTER TABLE Command

The ALTER TABLE command is a DDL statement used to modify the structure of an existing table. This includes adding, dropping, or modifying columns, as well as adding and dropping constraints.

The exact syntax for ALTER TABLE can vary significantly between different RDBMSs, but the core operations are similar.

Common ALTER TABLE Operations

Let’s assume we have a Products table to modify.

ADD a New Column

To add a new column to a table:

-- Add a 'StockQuantity' column to the Products table
ALTER TABLE Products
ADD COLUMN StockQuantity INT NOT NULL DEFAULT 0;

DROP a Column

To remove a column from a table (use with caution, as this is destructive):

-- Remove the 'StockQuantity' column we just added
ALTER TABLE Products
DROP COLUMN StockQuantity;

MODIFY a Column’s Data Type

To change the data type of an existing column. This may fail if existing data cannot be converted to the new type.

-- PostgreSQL / Standard Syntax
ALTER TABLE Products
ALTER COLUMN ProductName TYPE VARCHAR(150);

-- MySQL Syntax
ALTER TABLE Products
MODIFY COLUMN ProductName VARCHAR(150);

RENAME a Column

To change the name of a column:

ALTER TABLE Products
RENAME COLUMN ProductName TO ItemName;

RENAME a Table

To change the name of the entire table:

ALTER TABLE Products
RENAME TO Items;

Adding and Dropping Constraints

ALTER TABLE is also used to manage constraints after a table has been created.

ADD a Constraint

It’s good practice to name your constraints to make them easier to manage later.

-- Add a FOREIGN KEY constraint
ALTER TABLE Products
ADD CONSTRAINT fk_products_supplier
FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID);

-- Add a CHECK constraint
ALTER TABLE Products
ADD CONSTRAINT chk_price_positive CHECK (Price > 0);

DROP a Constraint

To remove a constraint, you refer to it by its name.

-- Drop the CHECK constraint we just added
ALTER TABLE Products
DROP CONSTRAINT chk_price_positive;

Important Considerations

Modifying large tables can be a slow and resource-intensive operation. On some database systems, an ALTER TABLE command may lock the entire table, making it unavailable to users for the duration of the operation. Always plan and test such changes carefully, especially in production environments. Modern databases and online schema change tools can often perform these operations with minimal downtime.