ALTER TABLE Statement

For SQL Server 6.5 information, see ALTER TABLE Statement in What's New for SQL Server 6.5.

Adds new columns or constraints to an existing table.

Syntax

ALTER TABLE [database.[owner].]table_name
[WITH NOCHECK]
[ADD
    {col_name column_properties [column_constraints]
    | [[,] table_constraint]}
        [, {next_col_name | next_table_constraint}]...]
|
[DROP [CONSTRAINT]
    constraint_name [, constraint_name2]...]

where

table_name
Specifies which table to alter. You can include local or global temporary tables, but FOREIGN KEY constraints are not enforced on temporary tables.
WITH NOCHECK
Allows CHECK or FOREIGN KEY constraints to be added to a table without verifying existing data for constraint violations. PRIMARY KEY and UNIQUE constraints are always checked. When this option is not specified (the default), any added constraints will be validated against existing data. If there are any constraint violations, the ALTER TABLE statement fails and a message is returned, stating the type of constraint and name that caused the violation. Use this option with extreme caution. This option is useful when you know your data already meets the new constraints or when a business rule requires the constraint to be enforced only from this point forward.

Note The WITH NOCHECK option will bypass checking FOREIGN KEY and CHECK constraints only at the time the table is altered. Future data modifications made against any column will demand that all columns satisfy all CHECK constraints, even those columns not included in the UPDATE column list.

ADD
Allows a column or table-level constraint to be added to an existing table.
col_name
Is a new column for the table. Column names must conform to the rules for identifiers and must be unique in the table.
column_properties =
datatype [NULL | IDENTITY[(seed, increment)]]
datatype
Specifies the datatype of the column. System or user-defined datatypes are acceptable. Columns added to a table must be defined as NULL. When a column is added, the initial value for the column will be set to NULL. This restriction forces the ALTER TABLE statement to fail if the bit or timestamp datatypes are used.
IDENTITY[(seed, increment)]
Generates values for existing rows based on the seed and increment parameters. If used, the seed value will be assigned to the first row in the table and each subsequent row will receive the next identity value, equal to the last identity plus the increment value. If neither argument is given, both default to 1.

Note If the maximum value, based on the datatype for the identity column, is exceeded during the generation of identity values, the ALTER TABLE statement fails and an error is returned.

The IDENTITY property cannot be added to an existing column; it can be added only to a new column. The IDENTITY property can be assigned a tinyint, smallint, int, decimal(p,0) or numeric(p,0) column that does not allow null values. Defaults and DEFAULT constraints cannot be bound to an identity column, and an identity value cannot be changed. Only one column per table can be defined as an identity column.

column_constraints =
Can include up to one UNIQUE or FOREIGN KEY constraint, one DEFAULT constraint, and any number of CHECK constraints for each new or existing column. All can be entered within the same ALTER TABLE statement.
For a column-level UNIQUE constraint:
[CONSTRAINT constraint_name]
UNIQUE [CLUSTERED | NONCLUSTERED] [(col_name)]
[WITH FILLFACTOR = fillfactor]
[ON segment_name]
For a column-level FOREIGN KEY constraint:
[CONSTRAINT constraint_name]
[FOREIGN KEY [(col_name)]]
REFERENCES [owner.]ref_table [(ref_col)]
For a column-level DEFAULT constraint:
[CONSTRAINT constraint_name]
DEFAULT {constant_expression | niladic-function | NULL}
For a column-level CHECK constraint:
[CONSTRAINT constraint_name]
CHECK [NOT FOR REPLICATION] (expression)
table_constraint =
Can include up to one PRIMARY KEY constraint per table, one DEFAULT constraint per column, and any number of FOREIGN KEY, UNIQUE, or CHECK constraints on any column or columns. All can be entered within the same ALTER TABLE statement.
For a table-level PRIMARY KEY constraint:
[CONSTRAINT constraint_name]
PRIMARY KEY [CLUSTERED | NONCLUSTERED]
(col_name [, col_name2 [..., col_name16]])
[WITH FILLFACTOR = fillfactor]
[ON segment_name]
For a table-level UNIQUE constraint:
[CONSTRAINT constraint_name]
UNIQUE [CLUSTERED | NONCLUSTERED]
(col_name [, col_name2 [..., col_name16]])
[WITH FILLFACTOR = fillfactor]
[ON segment_name]
For a table-level FOREIGN KEY constraint:
[CONSTRAINT constraint_name]
FOREIGN KEY (col_name [, col_name2 [..., col_name16]])
REFERENCES [owner.]ref_table [(ref_col [, ref_col2
[..., ref_col16]])]
For a table-level DEFAULT constraint:
[CONSTRAINT constraint_name]
DEFAULT {constant_expression | niladic-function | NULL}
FOR col_name
For a table-level CHECK constraint:
[CONSTRAINT constraint_name]
CHECK [NOT FOR REPLICATION] (expression)

Important When a PRIMARY KEY or UNIQUE constraint is added, an index is automatically created to enforce the constraint. If the constraint creates a clustered index, other indexes (previously created with CREATE INDEX or with other constraints) will need to be rebuilt by the system. Rebuilding indexes can be a potentially time-intensive operation with numerous concurrency ramifications. Whenever possible, make data definition changes when database activity is minimal.

CONSTRAINT constraint_name
Names the given constraint within the database. Constraint names must follow the rules for identifiers, except that the name cannot begin with a pound sign (#). If constraint_name is not supplied, a system-generated name is assigned to the constraint.
UNIQUE [CLUSTERED | NONCLUSTERED]
Provides entity integrity for a given column or columns. Although columns participating in UNIQUE constraints can allow null values, it is not recommended that you use null values. If null values are necessary, the complete key (a single column or multiple columns for a composite index) cannot be NULL for more than one row. Multiple UNIQUE constraints can be defined on a specific table. SQL Server automatically creates a UNIQUE index on this column or columns. UNIQUE key constraints can be dropped only by dropping the associated table or constraint. If no index type is specified, a NONCLUSTERED index is created by default.
WITH FILLFACTOR = fillfactor
Specifies how full SQL Server makes each index page when creating an index with existing data.
ON segment_name
Creates the index on the specified segment. If the index specified is a clustered index, the entire table will be moved to the specified segment. It is important that the segment have at least 1.2 times the space required for the entire table. For more information, see the CREATE INDEX statement.
[FOREIGN KEY [(col_name)]] REFERENCES [owner.]ref_table [(ref_col)]
or
FOREIGN KEY (col_name [, col_name2 [..., col_name16]])
REFERENCES [owner.]ref_table [(ref_col [, ref_col2 [..., ref_col16]])]
Provides single- or multicolumn referential integrity. When defined, the number of columns and datatypes of each column specified in the FOREIGN KEY clause must identically match the columns in the REFERENCES clause. Values entered in this column or columns must exist in the table, and column(s) defined in the REFERENCES clause and the referenced table's columns should have a PRIMARY KEY or UNIQUE constraint defined on them. For column-level constraints, using the FOREIGN KEY and col_name identifiers are optional.

When a FOREIGN KEY constraint is added to a table, all non-null values in the foreign key column(s) must reference an existing key within the referenced table. If any rows are invalid, the ALTER TABLE statement will fail.

Important REFERENCE constraints can reference only tables within the same database; this can include the same table on which the reference is defined (self-referenced tables). If you want cross-database referential integrity or custom messaging, implement these through triggers.

A table can have a maximum of 31 FOREIGN KEY constraints. This limit is an absolute upper limit; the maximum may be lower depending on the number of work tables the server has to create to enforce the constraint and varies by the type of query being executed. FOREIGN KEY constraints are not enforced for temporary tables.

FOREIGN KEY constraints, unlike PRIMARY KEY constraints, do not create an index. To improve performance of data retrieval operations, use the CREATE INDEX statement to create an index on a column with a FOREIGN KEY constraint. This will allow for quicker execution times when a referenced key is modified.

For a FOREIGN KEY to be successfully created, the user must have SELECT or REFERENCES permission on the referenced column or columns. For details, see the GRANT statement. When a specific reference column(s) is not provided, the primary key for the referenced table is used.

Note If REFERENCE constraints are added or altered, any stored procedures that reference the table will be recompiled. This allows changes made to a table definition (through the ALTER TABLE statement) to be visible to procedures created prior to the schema change.

DEFAULT constant_expression | niladic-function | NULL
Specifies the value that will be provided for the column when one is not explicitly supplied during an insert. A DEFAULT constraint can contain constant values, functions, built-in functions that do not take arguments (niladic-function), or NULL. A constant_expression must follow the same rules as defaults.

DEFAULT constraints can be added to columns that are not the timestamp datatype or have the IDENTITY property. If the column was defined with a user-defined datatype that has a default bound to it or if the column has a default bound to it, the DEFAULT constraint will not be allowed and the ALTER TABLE statement will fail. If a default or a DEFAULT constraint already exists for that column, it must be dropped before a DEFAULT constraint can be added. If the default exists on a user-defined datatype, the default must be unbound before that datatype can be used in a table definition with a DEFAULT constraint.

Niladic-functions allow a system-supplied value to be inserted when no value is specified. ANSI-standard niladic-functions include:

USER, CURRENT_USER, and SESSION_USER all default to the database username of the user performing the insert or update. SYSTEM_USER will provide the login ID, and CURRENT_TIMESTAMP will provide the same information as the GETDATE() function.

A benefit to using a DEFAULT constraint instead of a default (created with the CREATE DEFAULT statement) is that no explicit binding/unbinding is required and DEFAULT constraints are removed when the table is dropped.

When a DEFAULT is added for an existing column (as a table-level constraint), the column to which it applies is specified with FOR col_name.

CHECK (expression)
Enforces domain integrity by limiting the possible values that can be entered into a column or columns. The search condition must evaluate to a Boolean expression and cannot contain subqueries. CHECK constraints adhere to the same policies as rules, but they are automatically bound to the column(s) on which they are defined. A column-level CHECK constraint can only reference the constrained column, and a table-level CHECK constraint can only reference columns of the constrained table. Multiple CHECK constraints can be defined for a table; however, only one can be defined per column per ALTER TABLE statement (although each column constraint can have multiple conditions). When a rule and one or more CHECK constraints exist for a column or columns, all restrictions are evaluated. If defined on multiple columns, they must be defined as table-level constraints.
NOT FOR REPLICATION
Prevents the specified CHECK constraint from being enforced for the distribution process used by replication; however, CHECK constraints will be enforced for all other users (including the system administrator). NOT FOR REPLICATION is used to protect horizontally partitioned tables that receive "source" data from a publishing server from data modifications made to the replicated data. When replication is in effect, any modifications to the data that is replicated should be made on the publishing server (the main source of the data) and not on any of the subscribing servers (each of which receives exact copies of the publishing server's data).

The NOT FOR REPLICATION CHECK constraint will be applied to both the "before" and "after" image of an updated record to prevent records from being added to or deleted from the replicated range. All deletes and inserts will be checked; if they fall within the replicated range, they will be rejected.

When NOT FOR REPLICATION is used with ALTER TABLE, existing data is not checked to see if it meets the requirements of the constraint. The NOT FOR REPLICATION clause behaves as though the NOCHECK option were used on the column(s). All future data modifications, except those occurring through replication, will be checked.

PRIMARY KEY [CLUSTERED | NONCLUSTERED]
Enforces entity integrity only for an existing column or columns. New columns can only be added as a PRIMARY KEY if they have the identity property, because a new non-identity column cannot be defined NOT NULL and a PRIMARY KEY constraint must be NOT NULL. All columns changed to PRIMARY KEY constraint columns must have been defined as NOT NULL.

To enforce uniqueness of a primary key, SQL Server automatically creates a unique index on this column or columns. This unique index can be dropped only by dropping the associated table or PRIMARY KEY constraint.

If no index type is specified, a clustered index is created by default. If NONCLUSTERED is specified or if CLUSTERED is specified for a different UNIQUE constraint in the same statement block, a nonclustered index is created.

Only one PRIMARY KEY constraint can be specified for a given table. However, "alternate" or "candidate" keys can be effectively created with a UNIQUE constraint.

DROP CONSTRAINT constraint_name
Allows an existing column- or table-level constraint to be removed from the table definition.

Remarks

ALTER TABLE adds columns or constraints to a table, or drops constraints from a table, but it does not allow columns to be removed. When constraints are added, all existing data will be verified for constraint violations. If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use the WITH NOCHECK option; but this is not recommended.

The number of columns in a table cannot exceed 250. The maximum number of bytes per row is 1962, not including text or image columns, which are stored with their own separate chain of data pages.

Important If a stored procedure using SELECT * references a table that has been altered with new columns, the procedure (even if you use the WITH RECOMPILE option) does not recognize the columns you added to the table. In order for the new columns to be visible to the procedure, you must drop the stored procedure and then re-create it.

To rename a table, execute the sp_rename system stored procedure. To get information on a table and its columns, use the sp_help system stored procedure or the sp_helpconstraint system stored procedure.

Permission

ALTER TABLE permission defaults to the table owner. Permission cannot be transferred; however, the database owner can impersonate the table owner by using the SETUSER statement. The system administrator can also alter users' tables.

Examples

A.    Add a PRIMARY KEY Constraint

The authors table in SQL Server 6.0 includes a PRIMARY KEY constraint on the au_id column. This example shows how to add only this constraint (with an explicit name).

ALTER TABLE authors
ADD
CONSTRAINT UPKCL_auidind PRIMARY KEY CLUSTERED (au_id) 
B.    Add a FOREIGN KEY Constraint

The titles table in SQL Server 6.0 includes a foreign key reference to the authors table. This example adds only this constraint (allowing the system to supply a name).

ALTER TABLE titles
ADD
CONSTRAINT FK_pub_id FOREIGN KEY (pub_id) REFERENCES publishers(pub_id)
C.    Add a UNIQUE Constraint

The stores table has columns of stor_id, stor_name, and city, where stor_id is the primary key; however, no two stores in the same city should have the same name. This example adds only this constraint.

ALTER TABLE stores
ADD
CONSTRAINT UNC_name_city UNIQUE NONCLUSTERED (stor_name, city)
D.    Add a DEFAULT Constraint

The authors table has a phone column where a value is required. This example adds a default value of UNKNOWN for future inserts that do not explicitly enter a phone number.

ALTER TABLE authors
ADD
DEFAULT 'UNKNOWN' FOR phone
E.    Add a CHECK Constraint

The authors table has a zip column where a 5-digit character string is required. This example adds a CHECK constraint to guarantee that only numbers are entered.

ALTER TABLE authors
ADD
CONSTRAINT CK_zip CHECK (zip LIKE '[0-9][0-9][0-9][0-9][0-9]')
F.    Add a New Column with Constraints

In SQL Server 6.0, the publishers table has a new column called country with a default value of USA. This example adds the country column.

ALTER TABLE publishers
ADD
country varchar(30)        NULL
        DEFAULT('USA')
G.    Complex ALTER TABLE Statement with Multiple Changes

In SQL Server 6.0, the publishers table has a CHECK and PRIMARY KEY constraint on the pub_id column and a new column called country with a default value of USA. This example alters the table with all of these changes.

ALTER TABLE publishers
ADD
country varchar(30)        NULL
        DEFAULT('USA'),
CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED (pub_id) ,
CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
    OR pub_id LIKE '99[0-9][0-9]')
H.    Drop a Constraint

To drop a constraint, you must first know the constraint name. If the constraint names were not explicitly entered, use either the sp_help or the sp_helpconstraint system stored procedure to retrieve the system-supplied constraint names.

This example drops the UPKCL_auidind created in example A.

ALTER TABLE authors
    DROP CONSTRAINT UPKCL_auidind

See Also

CREATE TABLE sp_help
DROP TABLE sp_rename