ALTER TABLE Statement (T-SQL)

Purpose

The ALTER TABLE statement in Transact-SQL (T-SQL) is used to modify an existing table's structure. This includes adding, deleting, or modifying columns, constraints, and other table properties.

Syntax and Common Operations

1. Adding a Column

Adds a new column to an existing table.

ALTER TABLE TableName
ADD ColumnName DataType [NULL | NOT NULL] [DEFAULT DefaultValue];

Example: Adding an 'Email' column to the 'Customers' table.

ALTER TABLE Customers
ADD Email VARCHAR(255) NULL;

2. Dropping a Column

Removes a column from a table. Be cautious, as this permanently deletes all data in that column.

ALTER TABLE TableName
DROP COLUMN ColumnName;

Example: Dropping the 'PhoneNumber' column from the 'Customers' table.

ALTER TABLE Customers
DROP COLUMN PhoneNumber;

3. Modifying a Column's Data Type or Properties

Changes the data type, nullability, or default value of an existing column.

ALTER TABLE TableName
ALTER COLUMN ColumnName NewDataType [NULL | NOT NULL];

Example: Changing the 'CustomerID' column to be an integer.

ALTER TABLE Orders
ALTER COLUMN CustomerID INT NOT NULL;

4. Adding a Constraint

Adds a new constraint (e.g., PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK) to a table.

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName ConstraintType (ColumnList);

Example: Adding a foreign key constraint to the 'Orders' table referencing 'Customers'.

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID);

5. Dropping a Constraint

Removes an existing constraint from a table.

ALTER TABLE TableName
DROP CONSTRAINT ConstraintName;

Example: Dropping the primary key constraint from the 'Products' table.

ALTER TABLE Products
DROP CONSTRAINT PK_Products;

6. Adding a DEFAULT Constraint

Assigns a default value to a column if no value is explicitly provided during an INSERT operation.

ALTER TABLE TableName
ADD CONSTRAINT DF_ColumnName DEFAULT DefaultValue FOR ColumnName;

Example: Setting a default value for a 'Status' column.

ALTER TABLE Tasks
ADD CONSTRAINT DF_Status DEFAULT 'Pending' FOR Status;

Important Considerations

• Data Loss: Dropping columns or significantly altering data types can lead to data loss. Always back up your data before performing such operations.
• Dependencies: Ensure that the table modifications do not break existing views, stored procedures, or foreign key relationships.
• Performance: For large tables, operations like adding or dropping columns, especially those involving data manipulation, can be time-consuming and lock the table. Plan these operations during off-peak hours.
• Transaction Management: Wrap your ALTER TABLE statements within a transaction (BEGIN TRANSACTION ... COMMIT TRANSACTION / ROLLBACK TRANSACTION) to ensure atomicity and allow for rollback in case of errors.