Altering a Table

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:

  • 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 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 CHECK 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, changing nullability of the column (for example, VARCHAR(10) to VARCHAR(20)), or both.
  • 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 both.
  • 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 old type to 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:


 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) 

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 VALUESclause so that the existing rows of the table are filled with the specified default value.

Adding, Dropping, Disabling, or Enabling a Constraint

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:

  • 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) 
    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.
  • To enable or disable a constraint. In the next example, we're enabling all the constraints on the employee table:

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.

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 bound to a rule, accomplished via the following syntax:
  •  ALTER TABLE  table-name  DROP COLUMN  column-name  [,  next-column-name  ]... 
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.

Enabling or Disabling a Trigger

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.

Inside Microsoft SQL Server 7.0
Inside Microsoft SQL Server 7.0 (Mps)
ISBN: 0735605173
EAN: 2147483647
Year: 1999
Pages: 144 © 2008-2017.
If you may any questions please contact us: