T-SQL » Statements » ALTER TABLE

Syntax:
ALTER TABLE table
{
    ADD
    {
       column_name data_type [ ( size ) ]
       [ DEFAULT value ]
       { [ NULL | NOT NULL ] | [ { PRIMARY KEY | UNIQUE } ] }
       { [ CONSTRAINT constraint_name ] }
    }
    |
    ALTER COLUMN column_name
    {
       data_type [ ( size ) ] [ ( precision [ , scale ] ) ]
       [ NULL | NOT NULL ]
    }
    |
    DROP { COLUMN column_name | [ CONSTRAINT ] constraint_name }
}
table
Specifies which table is to be altered.
column_name
The name of the column being added, altered, or dropped.
data_type
The data type of the column being added or altered.
size
Is the length of the data that can be entered into a field.
DEFAULT value
Is the default value for the column being altered.
NULL | NOT NULL
Is a parameter that indicates whether a column can or cannot contain null values.
PRIMARY KEY
Is a parameter that identifies the column or set of columns whose values uniquely identify each row in a table. Each table can only have one primary key constraint.
UNIQUE
Is a constraint that enforces the uniqueness of the values in a set of columns.
constraint_name
The name of the constraint to be added or dropped.
precision
Specifies the precision for the data type.
scale
Specifies the scale for the data type.

The ALTER TABLE statement can be used to modify an existing table by adding, altering, or dropping columns and indexes.

Examples

Code:
ALTER TABLE Sales
ADD UnitPrice MONEY;
Output:
The command(s) completed successfully.
Explanation:

This example adds a single field to the 'Sales' table by specifying the new field name and data type.

Language(s): MS SQL Server
Code:
ALTER TABLE Sales
ADD UnitPrice MONEY
DEFAULT 3.00 NOT NULL;
Output:
(3 row(s) affected)
Explanation:

This example adds the reserved words NOT NULL to require valid data to be added to that field. However, you must also specify a default value for any data already inserted in the table.

Language(s): MS SQL Server
Code:
ALTER TABLE Sales
ALTER COLUMN ItemCount INT;
Output:
(3 row(s) affected)
Explanation:

This example changes the data type of the column 'ItemCount' to an integer (INT).

Language(s): MS SQL Server
Code:
ALTER TABLE Sales
DROP COLUMN UnitPrice;
Output:
The command(s) completed successfully.
Explanation:

This example demonstrates how to use DROP COLUMN to delete a single field.

Language(s): MS SQL Server
Code:
ALTER TABLE Sales
ADD Item VARCHAR (30)
CONSTRAINT UniqueConstraint UNIQUE;
Output:
The command(s) completed successfully.
Explanation:

This example defines an index for a new field by using the CONSTRAINT clause. Indexes cannot be created for the following data types: text, ntext, or image data type.

Note: The TEXT and INT data types cannot have a field size property set.

Language(s): MS SQL Server
Code:
ALTER TABLE Names
ADD CONSTRAINT UniqueValues
UNIQUE (FirstName, LastName);
Output:
The command(s) completed successfully.
Explanation:

This shows how ADD CONSTRAINT can be used to add a multi-field index.

Language(s): MS SQL Server
Code:
ALTER TABLE Names
DROP CONSTRAINT UniqueValues;
Output:
The command(s) completed successfully.
Explanation:

DROP CONSTRAINT can also be used to remove a multi-field index.

Language(s): MS SQL Server

See Also: