SQL Server 7 allows existing tables to be modified in several ways, and we've seen some of these methods already. You can use ALTER TABLE to add or drop constraints from a table. We'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:
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:
Here's the syntax and an example of using the ALTER COLUMN clause of the ALTER TABLE statement:
ALTER TABLE table-name ALTER COLUMN column-name { type_name [ ( prec [, scale ] ) ] [ NULL NOT NULL ] {ADD DROP} ROWGUIDCOL } |
/* 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) |
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 VALUESclause so that the existing rows of the table are filled with the specified default value.
Constraint modifications were covered 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:
ALTER TABLE orders WITH NOCHECK ADD FOREIGN KEY (cust_id) REFERENCES customer (cust_id) |
NOTE
We 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.
ALTER TABLE EMPLOYEE CHECK ALL |
One more commonly misunderstood aspect of using ALTER TABLE to drop constraints is that dropping a PRIMARY KEY or UNIQUE constraint will automatically drop the associated index. In fact, the only way to drop those indexes is by altering the table to remove the constraint.
You can use ALTER TABLE to remove one or more columns from a table. However, you can't drop the following columns:
ALTER TABLE table-name DROP COLUMN column-name [, next-column-name ]... |
NOTE
Notice the syntax difference between adding a new column and dropping a column: when adding a new column to a table, the word COLUMN isn't used, but when dropping a column, the word COLUMN is required.
You can enable or disable one or more (or all ) triggers on a table using the ALTER TABLE command. We'll look at this topic in more detail when we discuss triggers in Chapter 10.
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.