ALTER TABLE (Transact-SQL)

Applies to: SQL Server 2008 and later versions.

Syntax

Modifies the definition of an existing table. You can use ALTER TABLE to:

  • Add or drop constraints.
  • Enable or disable constraints.
  • Add, drop, or modify columns.
  • Add or drop extended properties.
ALTER TABLE [ schema_name. ] table_name { action [,...n] } [ WITH ] [ ; ] action ::= { table_constraint | column_def | { LIMITED | NODEFAULT } column_name | alter_column | add_table_options | alter_table_options } table_constraint ::= { [ CONSTRAINT ] { { PRIMARY KEY | UNIQUE | FOREIGN KEY | CHECK } [ ] [ ON ] } | { PRIMARY KEY | UNIQUE | FOREIGN KEY | CHECK } [ ] [ ON ] | foreign_key_constraint_definition | check_constraint_definition | default_constraint_definition | replication_period_constraint_definition } column_def ::= ALTER TABLE ADD [ ] [ ] [ FILEGROUP ] alter_column ::= ALTER COLUMN [ ] [ WITH ( [ ,...n ] ) ] add_table_options ::= ADD { FILEGROUP } alter_table_options ::= { ENABLE TRIGGER { ALL | [ ,...n ] } | DISABLE TRIGGER { ALL | [ ,...n ] } }

Parameters

schema_name: The name of the schema that owns the table.

table_name: The name of the table to be altered.

action: Specifies the action to be performed on the table. This can include adding or dropping constraints, adding or altering columns, etc.

options: Additional options that can be specified for the ALTER TABLE statement.

Examples

Adding a Column

This example adds a new column named EmailAddress to the Contact table.


ALTER TABLE Person.Contact
ADD EmailAddress varchar(255);

Adding a PRIMARY KEY Constraint

This example adds a primary key constraint named PK_EmployeeID to the Employees table on the EmployeeID column.


ALTER TABLE HumanResources.Employee
ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID);

Modifying a Column Data Type

This example changes the data type of the PhoneNumber column in the Contact table to varchar(20).


ALTER TABLE Person.Contact
ALTER COLUMN PhoneNumber varchar(20);

Dropping a Column

This example drops the FaxNumber column from the Contact table.


ALTER TABLE Person.Contact
DROP COLUMN FaxNumber;

Dropping a Constraint

This example drops the FK_Product_ProductSubcategory foreign key constraint from the Product table.


ALTER TABLE Production.Product
DROP CONSTRAINT FK_Product_ProductSubcategory;

Remarks

When you modify a column that is part of an index or constraint, the index or constraint is dropped and then re-created.

Be cautious when altering tables that contain large amounts of data, as some operations can be time-consuming and may lock the table.

Related Topics