>From the SQL documentation:
ALTER TABLE (T-SQL)
Modifies a table definition by altering, adding or dropping columns and
constraints, or by disabling or enabling constraints and triggers.
Syntax
ALTER TABLE table
{ [ALTER COLUMN column_name
{ new_data_type [ (precision[, scale] ) ]
[ NULL | NOT NULL ]
| {ADD | DROP} ROWGUIDCOL
}
]
| ADD
{ [ <column_definition> ]
| column_name AS computed_column_expression
}[,...n]
| [WITH CHECK | WITH NOCHECK] ADD
{ <table_constraint> }[,...n]
| DROP
{ [CONSTRAINT] constraint_name
| COLUMN column
}[,...n]
| {CHECK | NOCHECK} CONSTRAINT
{ALL | constraint_name[,...n]}
| {ENABLE | DISABLE} TRIGGER
{ALL | trigger_name[,...n]}
}
<column_definition> ::= { column_name data_type }
[ [ DEFAULT constant_expression ]
| [ IDENTITY [(seed, increment ) [NOT FOR REPLICATION] ] ]
]
[ ROWGUIDCOL ]
[ <column_constraint>] [ ...n]
<column_constraint> ::= [CONSTRAINT constraint_name]
{
[ NULL | NOT NULL ]
| [ { PRIMARY KEY | UNIQUE }
[CLUSTERED | NONCLUSTERED]
[WITH FILLFACTOR = fillfactor]
[ON {filegroup | DEFAULT} ]]
]
| [ [FOREIGN KEY]
REFERENCES ref_table [(ref_column) ]
[NOT FOR REPLICATION]
]
| CHECK [NOT FOR REPLICATION]
(logical_expression)
}
<table_constraint> ::= [CONSTRAINT constraint_name]
{ [ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED]
{ ( column[,...n] ) }
[ WITH FILLFACTOR = fillfactor]
[ON {filegroup | DEFAULT} ]
]
| FOREIGN KEY
[(column[,...n])]
REFERENCES ref_table [(ref_column[,...n])]
[NOT FOR REPLICATION]
| DEFAULT constant_expression
[FOR column]
| CHECK [NOT FOR REPLICATION]
(logical_expression)
}
Arguments
table
Is the name of the table to be altered. If the table is not in the current
database or owned by the current user, the database and owner can be
explicitly specified.
WITH CHECK | WITH NOCHECK
Specifies whether the data in the table is or is not validated against a
newly added or reenabled FOREIGN KEY or CHECK constraint. If not specified,
WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for
reenabled constraints.
If you do not want to verify new CHECK or FOREIGN KEY constraints against
existing data, use WITH NOCHECK. (This is not recommended except in rare
cases). The new constraint will be evaluated in all future updates. Any
constraint violations that are suppressed by WITH NOCHECK when the
constraint is added may cause future updates to fail if they update rows
with data that does not comply with the constraint.
Constraints defined WITH NOCHECK are not considered by the query optimizer.
These constraints are ignored until all such constraints are reenabled using
ALTER TABLE table CHECK CONSTRAINT ALL.
ALTER COLUMN
Specifies that the given column is to be changed or altered. ALTER COLUMN is
not allowed if the compatibility level is 65 or earlier. For more
information, see sp_dbcmptlevel. The altered column cannot be:
A column with a text, image, ntext, or timestamp data type.
The ROWGUIDCOL for the table.
A computed column or be used in a computed column.
A replicated column.
Used in an index, unless the column is a varchar or varbinary data type, the
data type is not changed, and the new size is equal to or larger than the
old size.
Used in statistics generated by the CREATE STATISTICS statement. First
remove the statistics using the DROP STATISTICS statement. Statistics
automatically generated by the query optimizer are automatically dropped by
ALTER COLUMN.
Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.
Used in a CHECK or UNIQUE constraint, except that altering the length of a
variable-length column used in a CHECK or UNIQUE constraint is allowed.
Associated with a default, except that changing the length, precision, or
scale of a column is allowed if the data type is not changed.
Some data type changes may result in a change in the data. For example,
changing an nchar or nvarchar column to char or varchar can result in the
conversion of extended characters. For more information, see CAST and
Convert. Reducing the precision and scale of a column may result in data
truncation.
column_name
Is the name of the column to be altered, added or dropped. For new columns,
column_name can be omitted for columns created with a timestamp data type.
The name timestamp is used if no column_name is specified for a timestamp
data type column.
new_data_type
Is the new data type for the altered column. Criteria for the new_data_type
of an altered column are:
The previous data type must be implicitly convertible to the new data type.
new_data_type cannot be timestamp.
ANSI null defaults are always on for ALTER COLUMN; if not specified, the
column is nullable.
ANSI padding is always on for ALTER COLUMN.
If the altered column is an identity column, new_data_type must be a data
type that supports the identity property.
The current setting for SET ARITHABORT is ignored. ALTER TABLE operates as
if the ARITHABORT option is ON.
precision
Is the precision for the specified data type. For more information about
valid precision values, see Precision, Scale, and Length.
scale
Is the scale for the specified data type. For more information about valid
scale values, see Precision, Scale, and Length.
NULL | NOT NULL
Specifies whether the column can accept null values. Columns that do not
allow null values can be added with ALTER TABLE only if they have a default
specified. A new column added to a table must either allow null values, or
the column must be specified with a default value.
If the new column allows null values and no default is specified, the new
column contains a null value for each row in the table. If the new column
allows null values and a default definition is added with the new column,
the WITH VALUES option can be used to store the default value in the new
column for each existing row in the table.
If the new column does not allow null values, a DEFAULT definition must be
added with the new column, and the new column automatically loads with the
default value in the new columns in each existing row.
NULL can be specified in ALTER COLUMN to make a NOT NULL column allow null
values, except for columns in PRIMARY KEY constraints. NOT NULL can be
specified in ALTER COLUMN only if the column contains no null values. The
null values must be updated to some value before the ALTER COLUMN NOT NULL
is allowed, such as:
UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL
If NULL or NOT NULL is specified with ALTER COLUMN, new_data_type
[(precision [, scale ])] must also be specified. If the data type, precision
and scale are not being changed, specify the current values for the column.
[ {ADD | DROP} ROWGUIDCOL ]
Specifies the ROWGUIDCOL property is being added to or dropped from the
specified column. ROWGUIDCOL is a keyword indicating that the column is a
row global unique identifier column. Only one uniqueidentifier column per
table can be designated as the ROWGUIDCOL column. The ROWGUIDCOL property
can be assigned only to a uniqueidentifier column.
The ROWGUIDCOL property does not enforce uniqueness of the values stored in
the column. It also does not automatically generate values for new rows
inserted into the table. To generate unique values for each column, either
use the NEWID function on INSERT statements or specify the NEWID function as
the default for the column.
ADD
Specifies that one or more column definitions, computed column definitions,
or table constraints are being added.
computed_column_expression
Is an expression that defines the value of a computed column. A computed
column is a virtual column that is not physically stored in the table, but
it is computed from an expression using other columns in the same table. For
example, a computed column could have the definition: cost AS price * qty.
The expression can be a noncomputed column name, constant, function,
variable, and any combination of these connected by one or more operators.
The expression cannot be a subquery.
Computed columns can be used in select lists, WHERE clauses, ORDER BY
clauses, or any other locations where regular expressions can be used, with
these exceptions:
A computed column cannot be used as a key column in an index or as part of
any PRIMARY KEY, UNIQUE, FOREIGN KEY, or DEFAULT constraint definition.
A computed column cannot be the target of an INSERT or UPDATE statement.
----------------------------------------------------------------------------
----
Note Because each row in a table may have different values for columns
involved in a computed column, the computed column may not have the same
result for each row.
----------------------------------------------------------------------------
----
n
Is a placeholder indicating that the preceding item can be repeated n number
of times.
DROP { [CONSTRAINT] constraint_name | COLUMN column_name }
Specifies that constraint_name or column_name is removed from the table.
DROP COLUMN is not allowed if the compatibility level is 65 or earlier.
Multiple columns and constraints can be listed. A column cannot be dropped
if it is:
A replicated column.
Used in an index.
Used in a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint.
Associated with a default defined with the DEFAULT keyword, or bound to a
default object.
Bound to a rule.
{ CHECK | NOCHECK} CONSTRAINT
Specifies that constraint_name is enabled or disabled. When disabled, future
inserts or updates to the column are not validated against the constraint
conditions. This option can only be used with FOREIGN KEY and CHECK
constraints.
ALL
Specifies that all constraints are disabled with the NOCHECK option, or
enabled with the CHECK option.
{ENABLE | DISABLE} TRIGGER
Specifies that trigger_name is enabled or disabled. When a trigger is
disabled it is still defined for the table; however, when INSERT, UPDATE or
DELETE statements are executed against the table, the actions in the trigger
are not performed until the trigger is reenabled.
ALL
Specifies that all triggers in the table are enabled or disabled.
trigger_name
Specifies the name of the trigger to disable or enable.
column_name data_type
Is the data type for the new column. data_type can be any Microsoft® SQL
Server™ or user-defined data type.
DEFAULT
Is a keyword that specifies the default value for the column. DEFAULT
definitions can be used to provide values for a new column in the existing
rows of data. DEFAULT definitions cannot be added to columns that have a
timestamp data type, an IDENTITY property, an existing DEFAULT definition,
or a bound default. If the column has an existing default, the default must
be dropped before the new default can be added. To maintain compatibility
with earlier versions of SQL Server, it is possible to assign a constraint
name to a DEFAULT.
IDENTITY
Specifies that the new column is an identity column. When a new row is added
to the table, SQL Server provides a unique, incremental value for the
column. Identity columns are commonly used in conjunction with PRIMARY KEY
constraints to serve as the unique row identifier for the table. The
IDENTITY property can be assigned to a tinyint, smallint, int, decimal(p,0),
or numeric(p,0) column. Only one identity column can be created per table.
The DEFAULT keyword and bound defaults cannot be used with an identity
column. Either both the seed and increment must be specified, or neither. If
neither are specified, the default is (1,1).
seed
Is the value that is used for the first row loaded into the table.
increment
Is the incremental value that is added to the identity value of the previous
row that was loaded.
NOT FOR REPLICATION
Specifies that the IDENTITY property should not be enforced when a
replication login, such as sqlrepl, inserts data into the table. NOT FOR
REPLICATION can also be specified on constraints. The constraint is not
checked when a replication login inserts data into the table.
CONSTRAINT
Specifies the beginning of a PRIMARY KEY, UNIQUE, FOREIGN KEY, or CHECK
constraint, or a DEFAULT definition.
constraint_name
Is the name of the new constraint. Constraint names must follow the rules
for identifiers, except that the name cannot begin with a number sign (#).
If constraint_name is not supplied, a system-generated name is assigned to
the constraint.
PRIMARY KEY
Is a constraint that enforces entity integrity for a given column or columns
through a unique index. Only one PRIMARY KEY constraint can be created per
table.
UNIQUE
Is a constraint that provides entity integrity for a given column or columns
through a unique index.
CLUSTERED | NONCLUSTERED
Specifies that a clustered or nonclustered index is created for the PRIMARY
KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED;
UNIQUE constraints default to NONCLUSTERED.
If a clustered constraint or index already exists on a table, CLUSTERED
cannot be specified in ALTER TABLE. If a clustered constraint or index
already exists on a table, PRIMARY KEY constraints default to NONCLUSTERED.
WITH FILLFACTOR = fillfactor
Specifies how full SQL Server should make each index page used to store the
index data. User-specified fillfactor values can be from 1 through 100. If a
value is not specified, the default is 0. A lower fillfactor creates the
index with more space available for new index entries without having to
allocate new space. For more information, see CREATE INDEX.
ON {filegroup | DEFAULT}
Specifies the storage location of the index that is created for the
constraint. If filegroup is specified, the index is created in the named
filegroup. If DEFAULT is specified, the index is created in the default
filegroup. If ON is not specified, the index is created in the filegroup
that contains the table. If ON is specified when adding a clustered index
for a PRIMARY KEY or UNIQUE constraint, the entire table is moved to the
specified filegroup when the clustered index is created.
FOREIGN KEY...REFERENCES
Is a constraint that provides referential integrity for the data in the
column. FOREIGN KEY constraints require that each value in the column exists
in the specified column in the referenced table.
ref_table
Is the name of the table that is referenced by the FOREIGN KEY constraint.
ref_column
Is a column or list of columns in parentheses that is referenced by the new
FOREIGN KEY constraint.
WITH VALUES
Specifies that the value given in DEFAULT constant_expression is stored in a
new column added to existing rows. WITH VALUES can be specified only when
DEFAULT is specified in an ADD column clause. If the added column allows
null values and WITH VALUES is specified, the default value is stored in the
new column added to existing rows. If WITH VALUES is not specified for
columns that allow nulls, the value NULL is stored in the new column in
existing rows. If the new column does not allow nulls, the default value is
stored in new rows regardless of whether WITH VALUES is specified.
constant_expression
Is a literal value, a NULL, or a system function that is used as the default
value for the column.
CHECK
Is a constraint that enforces domain integrity by limiting the possible
values that can be entered into a column or columns.
logical_expression
Is a logical expression that is used in a CHECK constraint and returns TRUE
or FALSE. Logical_expression used with CHECK constraints cannot reference
another table, but can reference other columns in the same table for the
same row.
column[,...n]
Is a column or list of columns in parentheses used in a new constraint.
FOR column
Specifies the column associated with a table-level DEFAULT definition.
Remarks
To add new rows of data, use the INSERT statement. To remove rows of data,
use the DELETE or TRUNCATE TABLE statements. To change the values in
existing rows, use UPDATE.
The changes specified in ALTER TABLE are implemented immediately. If the
changes require modifications of the rows in the table, ALTER TABLE updates
the rows. ALTER TABLE acquires a schema modify lock on the table to ensure
no other connections reference even the metadata for the table during the
change. The modifications made to the table are logged and fully
recoverable. Changes that affect all the rows in very large tables, such as
dropping a column or adding a NOT NULL column with a default, can take a
long time to complete and generate many log records. These ALTER TABLE
statements should be executed with the same care as any INSERT, UPDATE, or
DELETE statement that affects a large number of rows.
If there are any execution plans in the procedure cache referencing the
table, ALTER TABLE marks them to be recompiled on their next execution.
Indexes created as part of a constraint are dropped when the constraint is
dropped. Indexes that were created with CREATE INDEX must be dropped with
the DROP INDEX statement. The DBCC DBREINDEX statement can be used to
rebuild an index that is part of a constraint definition; the constraint
does not need to be dropped and added again with ALTER TABLE.
All indexes and constraints based on a column must be removed before the
column can be removed.
When constraints are added, all existing data is verified for constraint
violations. If any violations occur, the ALTER TABLE statement fails and an
error is returned.
When a new PRIMARY KEY or UNIQUE constraint is added to an existing column,
the data in the column(s) must be unique. If duplicate values are found, the
ALTER TABLE statement fails. The WITH NOCHECK option has no effect when
adding PRIMARY KEY or UNIQUE constraints.
Each PRIMARY KEY and UNIQUE constraint generates an index. The number of
UNIQUE and PRIMARY KEY constraints cannot cause the number of indexes on the
table to exceed 249 nonclustered indexes and 1 clustered index.
If a column is added having a uniqueidentifier data type, it can be defined
with a default that uses the NEWID() function to supply the unique
identifier values in the new column for each existing row in the table.
SQL Server does not enforce an order in which DEFAULT, IDENTITY, ROWGUIDCOL,
or column constraints are specified in a column definition.
The ALTER COLUMN clause of ALTER TABLE does not bind or unbind any rules on
a column. Rules must be bound or unbound separately using sp_bindrule or
sp_unbindrule.
Rules can be bound to a user-defined data type. CREATE TABLE then
automatically binds the rule to any column defined having the user-defined
data type. ALTER COLUMN does not unbind the rule when changing the data type
of the column. The rule from the original user-defined data type remains
bound to the column. After ALTER COLUMN has changed the data type of the
column, any subsequent sp_unbindrule execution that unbinds the rule from
the user-defined data type does not unbind it from the column for which data
type was changed. If ALTER COLUMN changes the data type of a column to a
user-defined data type that is bound to a rule, the rule bound to the new
data type is not bound to the column.
Permissions
ALTER TABLE permissions default to the table owner, members of the sysadmin
fixed server role, and to members of the db_owner and db_ddladmin fixed
database roles.
Examples
A. Alter a table to add a new column
This example adds a column that allows null values and has no values
provided through a DEFAULT definition. Each row will have a NULL in the new
column.
CREATE TABLE doc_exa ( column_a INT)
GO
ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
GO
EXEC sp_help doc_exa
GO
DROP TABLE doc_exa
GO
B. Alter a table to drop a column
This example modifies a table to remove a column.
CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)
GO
ALTER TABLE doc_exb DROP COLUMN column_b
GO
EXEC sp_help doc_exb
GO
DROP TABLE doc_exb
GO
C. Alter a table to add a column with a constraint
This example adds a new column with a UNIQUE constraint.
CREATE TABLE doc_exc ( column_a INT)
GO
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE
GO
EXEC sp_help doc_exc
GO
DROP TABLE doc_exc
GO
D. Alter a table to add a constraint that is not verified
This example adds a constraint to an existing column in the table. The
column has a value that violates the constraint; therefore, WITH NOCHECK is
used to prevent the constraint from being validated against existing rows,
and to allow the constraint to be added.
CREATE TABLE doc_exd ( column_a INT)
GO
INSERT INTO doc_exd VALUES (-1)
GO
ALTER TABLE doc_exd WITH NOCHECK
ADD CONSTRAINT exd_check CHECK (column_a > 1)
GO
EXEC sp_help doc_exd
GO
DROP TABLE doc_exd
GO
E. Alter a table to add several columns with constraints
This example adds several columns with constraints that are defined with the
new column. The first new column has an IDENTITY property; each row in the
table has new incremental values in the identity column.
CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE)
GO
ALTER TABLE doc_exe ADD
/* Add a PRIMARY KEY identity column. */
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY,
/* Add a column referencing another column in the same table. */
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),
/* Add a column with a constraint to enforce that */
/* nonnull data is in a valid phone number format. */
column_d VARCHAR(16) NULL
CONSTRAINT column_d_chk
CHECK
(column_d IS NULL OR
column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR
column_d LIKE
"([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),
/* Add a nonnull column with a default. */
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081
GO
EXEC sp_help doc_exe
GO
DROP TABLE doc_exe
GO
F. Add a nullable column with default values
This example adds a nullable column with a DEFAULT definition, and uses WITH
VALUES to provide values for each existing row in the table. If WITH VALUES
is not used, each row has the value NULL in the new column.
ALTER TABLE MyTable
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT getdate() WITH VALUES
G. Disable and reenable a constraint
This example disables a constraint that limits the salaries that are
accepted in the data. WITH NOCHECK CONSTRAINT is used with ALTER TABLE to
disable the constraint and allow an insert that would normally violate the
constraint. WITH CHECK CONSTRAINT is used to reenable the constraint.
CREATE TABLE cnst_example
(id INT NOT NULL,
name VARCHAR(10) NOT NULL,
salary MONEY NOT NULL
CONSTRAINT salary_cap CHECK (salary < 100000)
)
-- Valid inserts
INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)
-- This insert violates the constraint.
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
-- Disable the constraint and try again.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
-- Reenable the constraint and try another insert, will fail.
ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (4,"Eric James",110000)
H. Disable and reenable a trigger
This example uses the DISABLE TRIGGER option of ALTER TABLE to disable the
trigger and allow an insert that would normally violate the trigger. It then
uses ENABLE TRIGGER to reenable the trigger.
CREATE TABLE trig_example
(id INT,
name VARCHAR(10),
salary MONEY)
go
-- Create the trigger.
CREATE TRIGGER trig1 ON trig_example FOR INSERT
as
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
print "TRIG1 Error: you attempted to insert a salary > $100,000"
ROLLBACK TRANSACTION
END
GO
-- Attempt an insert that violates the trigger.
INSERT INTO trig_example VALUES (1,"Pat Smith",100001)
GO
-- Disable the trigger.
ALTER TABLE trig_example DISABLE TRIGGER trig1
GO
-- Attempt an insert that would normally violate the trigger
INSERT INTO trig_example VALUES (2,"Chuck Jones",100001)
GO
-- Reenable the trigger.
ALTER TABLE trig_example ENABLE TRIGGER trig1
GO
-- Attempt an insert that violates the trigger.
INSERT INTO trig_example VALUES (3,"Mary Booth",100001)
GO
See Also
DROP TABLE sp_help
(c) 1988-98 Microsoft Corporation. All Rights Reserved.
------------------------------------
Mark Warrick - Fusioneers.com
Email: [EMAIL PROTECTED]
Phone: 714-547-5386
http://www.fusioneers.com
http://www.warrick.net
====================================
> -----Original Message-----
> From: Dave [mailto:[EMAIL PROTECTED]]
> Sent: Saturday, June 09, 2001 12:37 PM
> To: CF-Talk
> Subject: OT: Alter table
>
>
> Sorry for OT; working at home, manuals at work.
>
> SQL 7.0 - what's the format of the alter table stmt to
> increase the size of a varchar column??
>
> TIA
> Dave
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
