sql_alter

SQL ALTER TABLE Statement

The ALTER TABLE Statement

The ALTER TABLE statement is used to modify an existing table’s structure. This includes adding, deleting, or modifying columns, as well as adding or dropping constraints.

Common ALTER TABLE operations:

• Adding a new column
• Dropping an existing column
• Modifying the data type or properties of an existing column (e.g., size, NULLability, default value)
• Adding or dropping constraints (e.g., PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK)

Important: Modifying table structure, especially on large tables, can be a resource-intensive operation and may lock the table, impacting performance. It’s often recommended to perform such changes during maintenance windows.

SQL ALTER TABLE Syntax

The exact syntax for ALTER TABLE can vary between different database systems (RDBMS). Below are common syntaxes for typical operations.

1. Add a Column

ALTER TABLE table_name
ADD COLUMN column_name datatype [column_constraints];

Example: ALTER TABLE Employees ADD COLUMN HireDate DATE;

2. Drop a Column

ALTER TABLE table_name
DROP COLUMN column_name;

Note: Some older database systems might have restrictions on dropping columns, but most modern RDBMS support this. Be cautious as this permanently removes the column and its data.

Example: ALTER TABLE Employees DROP COLUMN FaxNumber;

3. Modify a Column’s Data Type or Properties

This syntax varies significantly across RDBMS:

SQL Server / PostgreSQL:

ALTER TABLE table_name
ALTER COLUMN column_name new_datatype [new_properties];

Example (SQL Server): ALTER TABLE Products ALTER COLUMN ProductName VARCHAR(100) NOT NULL;

MySQL:

ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype [new_properties];

Example (MySQL): ALTER TABLE Products MODIFY COLUMN ProductName VARCHAR(100) NOT NULL;

Oracle:

ALTER TABLE table_name
MODIFY (column_name new_datatype [new_properties]);

Example (Oracle): ALTER TABLE Products MODIFY (ProductName VARCHAR2(100 CHAR) NOT NULL);

4. Add a Constraint

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_definition;

Example: ALTER TABLE Orders ADD CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

5. Drop a Constraint

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Note: For some RDBMS like MySQL, to drop a FOREIGN KEY, you might need DROP FOREIGN KEY constraint_name. To drop a PRIMARY KEY, it might be DROP PRIMARY KEY.

Example: ALTER TABLE Orders DROP CONSTRAINT FK_CustomerOrder;

SQL ALTER TABLE Examples

Consider an initial “Persons” table:

PersonID | LastName | FirstName | Address      | City
-------- | -------- | --------- | ------------ | ---------
1        | Hansen   | Ola       | Timoteivn 10 | Sandnes
2        | Svendsen | Tove      | Borgvn 23    | Sandnes
3        | Pettersen| Kari      | Storgt 20    | Stavanger

Example 1: Add a ‘DateOfBirth’ column

ALTER TABLE Persons
ADD COLUMN DateOfBirth DATE NULL;

The “Persons” table will now look like this (new column initially NULL for existing rows):

PersonID | LastName | FirstName | Address      | City      | DateOfBirth
-------- | -------- | --------- | ------------ | --------- | -----------
1        | Hansen   | Ola       | Timoteivn 10 | Sandnes   | NULL
2        | Svendsen | Tove      | Borgvn 23    | Sandnes   | NULL
3        | Pettersen| Kari      | Storgt 20    | Stavanger | NULL

For a complete reference of data types, consult the documentation for your specific RDBMS (e.g., MS Access, MySQL, SQL Server, PostgreSQL, Oracle). Common types include VARCHAR(n), INT, DECIMAL(p,s), DATE, TIMESTAMP.

Example 2: Change Data Type / Properties of ‘Address’ column (MySQL example)

Suppose we want to increase the length of the Address column and make it NOT NULL (assuming it currently allows NULLs and we’ve updated existing NULLs).

ALTER TABLE Persons
MODIFY COLUMN Address VARCHAR(255) NOT NULL;

Note: Changing a column to NOT NULL requires that all existing rows have a non-NULL value for that column, or the operation will fail.

Example 3: Drop the ‘DateOfBirth’ column

ALTER TABLE Persons
DROP COLUMN DateOfBirth;

The “Persons” table will revert to its structure before ‘DateOfBirth’ was added.

Best Practices for ALTER TABLE:

• Backup Data: Always back up your table or database before making structural changes, especially in production environments.
• Test in Development: Test ALTER TABLE statements in a development or staging environment first.
• Maintenance Window: Perform alterations on large tables during off-peak hours or scheduled maintenance windows to minimize impact on users.
• Understand Implications: Be aware of how changes might affect existing data, applications, indexes, and constraints.
• One Change at a Time: While some RDBMS allow multiple alterations in one statement, it’s often clearer and safer to apply one structural change per ALTER TABLE statement, especially for complex changes.