Altering a Table | Inside Microsoft SQL Server 2000

SQL Server 2000 allows existing tables to be modified in several ways, and you've seen some of these methods already. You can use ALTER TABLE to add or drop constraints from a table. You've seen that some types of constraints have the option of not being applied to existing data by using the WITH NOCHECK option. Using the ALTER table command, you can make the following types of changes to an existing table:

Change the datatype or NULL property of a single column.
Add one or more new columns, with or without defining constraints for those columns.
Add one or more constraints.
Drop one or more constraints.
Drop one or more columns.
Enable or disable one or more constraints (only applies to CHECK and FOREIGN KEY constraints).
Enable or disable one or more triggers.

Changing a Datatype

By using the ALTER COLUMN clause of ALTER TABLE, you can modify the datatype or NULL property of an existing column. But be aware of the following restrictions:

The modified column can't be a text, image, ntext, or rowversion (timestamp) column.
If the modified column is the ROWGUIDCOL for the table, only DROP ROWGUIDCOL is allowed; no datatype changes are allowed.
The modified column can't be a computed or replicated column.
The modified column can't have a PRIMARY KEY or FOREIGN KEY constraint defined on it.
The modified column can't be referenced in a computed column.
The modified column can't have the type changed to timestamp.
If the modified column participates in an index, the only type changes that are allowed are increasing the length of a variable-length type (for example, VARCHAR(10) to VARCHAR(20)), changing nullability of the column, or both.
If the modified column has a UNIQUE OR CHECK constraint defined on it, the only change allowed is altering the length of a variable-length column. For a UNIQUE constraint, the new length must be greater than the old length.
If the modified column has a default defined on it, the only changes that are allowed are increasing or decreasing the length of a variable-length type, changing nullability, or changing the precision or scale.
The old type of the column should have an allowed implicit conversion to the new type.
The new type always has ANSI_PADDING semantics if applicable, regardless of the current setting.
If conversion of an old type to a new type causes an overflow (arithmetic or size), the ALTER TABLE statement is aborted.

Here's the syntax and an example of using the ALTER COLUMN clause of the ALTER TABLE statement:

SYNTAX

 ALTER TABLE table-name ALTER COLUMN column-name { type_name [ ( prec [, scale] ) ] [COLLATE <collation name> ] [ NULL | NOT NULL ] | {ADD | DROP} ROWGUIDCOL }

EXAMPLE

 /* Change the length of the emp_name column in the employee table from varchar(30) to varchar(50) */ ALTER TABLE employee ALTER COLUMN emp_name varchar(50)

Adding a New Column

You can add a new column, with or without specifying column-level constraints. You can add only one column for each ALTER TABLE statement. If the new column doesn't allow NULLs and isn't an identity column, the new column must have a default constraint defined. SQL Server populates the new column in every row with a NULL, the appropriate identity value, or the specified default. If the newly added column is nullable and has a default constraint, the existing rows of the table are not filled with the default value, but rather with NULL values. You can override this restriction by using the WITH VALUES clause so that the existing rows of the table are filled with the specified default value.

Adding, Dropping, Disabling, or Enabling a Constraint

I covered constraint modifications earlier in the discussion about constraints. The trickiest part of using ALTER TABLE to manipulate constraints is that the word CHECK can be used in three different ways:

To specify a CHECK constraint.
To defer checking of a newly added constraint. In the following example, we're adding a constraint to validate that cust_id in orders matches a cust_id in customer, but we don't want the constraint applied to existing data:
```
 ALTER TABLE orders WITH NOCHECK ADD FOREIGN KEY (cust_id) REFERENCES customer (cust_id) 
```
NOTE
I could also use WITH CHECK to force the constraint to be applied to existing data, but that's unnecessary because it's the default behavior.
To enable or disable a constraint. In the next example, we're enabling all the constraints on the employee table:
```
 ALTER TABLE EMPLOYEE CHECK CONSTRAINT ALL 
```

You need to be aware when using ALTER TABLE to drop constraints that dropping a PRIMARY KEY or UNIQUE constraint automatically drops the associated index. In fact, the only way to drop those indexes is by altering the table to remove the constraint.

NOTE
You can't use ALTER TABLE to modify a constraint definition. You must use ALTER TABLE to drop the constraint and then use ALTER TABLE to add a new constraint with the new definition.

Dropping a Column

You can use ALTER TABLE to remove one or more columns from a table. However, you can't drop the following columns:

A replicated column.
A column used in an index.
A column used in a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint.
A column associated with a default defined with the DEFAULT keyword or bound to a default object.
A column to which a rule is bound.

Dropping a column is accomplished using the following syntax:

 ALTER TABLE table-name DROP COLUMN column-name [, next-column-name]...

NOTE
Notice the syntax difference between dropping a column and adding a new column: the word COLUMN is required when dropping a column, but not when adding a new column to a table.

Enabling or Disabling a Trigger

You can enable or disable one or more (or all) triggers on a table using the ALTER TABLE command. I'll look at this topic in more detail when I discuss triggers in Chapter 12.

Note that not all the ALTER TABLE variations require SQL Server to change every row when the ALTER TABLE is issued. In many cases, SQL Server can just change the metadata (in syscolumns) to reflect the new structure. In particular, the data isn't touched when a column is dropped, when a new column is added and NULL is assumed as the new value for all rows, when the length of a variable-length column is changed, or when a non-nullable column is changed to allow NULLs. All other changes to a table's structure require SQL Server to physically update every row and to write the appropriate records to the transaction log.