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.
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
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.
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.
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.
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 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.
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.
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.
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.
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.
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)
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)
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)
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
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]')
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')
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]')
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
| CREATE TABLE | sp_help |
| DROP TABLE | sp_rename |