Certain errors are generated only by the alter table modify command. Although alter table modify is used to convert columns to compatible datatypes, alter table may issue errors if the columns you are converting have certain restrictions.
Make sure you understand the implications of modifying
a datatype before you issue the command. Generally, use alter
table modify only to implicitly convert between convertible
datatypes. This ensures that any hidden conversions required during
processing of insert and update statements
do not fail because of datatype incompatibility.
For example, if you add a second_advance column to the titles table with a datatype of int, and create a clustered index on second_advance, you cannot then modify this column to a char datatype. This would cause the int values to be converted from integers (1, 2, 3) to strings (‘1’, ‘2’, ‘3’). When the index is rebuilt with sorted data, the data values are expected to be in sorted order. But in this example, the datatype has changed from int to char and is no longer in sorted order for the char datatype’s ordering sequence. So, the alter table command fails during the index rebuild phase.
Be very cautious when choosing a new datatype for columns that are part of index key columns of clustered indexes. alter table modify must specify a target datatype that will not violate the ordering sequence of the modified data values after its data copy phase.
alter table modify also issues a warning message if you modify the datatype to an incompatible datatype in a column that contains a constraint. For example, if you try to modify from datatype char to datatype int, and the column includes a constraint, alter table modify issues this warning:
Warning: a rule or constraint is defined on column ‘new_col’ being modified. Verify the validity of rules and constraints after this ALTER TABLE operation.
This warning indicates that there might be some datatype inconsistency in the value that the constraint expects and the modified datatype of the column new_col:
Warning: column ‘new_col’ is referenced by one or more rules or constraints. Verify the validity of the rules/constraints after this ALTER TABLE operation.
If you attempt to insert data that is a datatype char into this table, it fails with this message:
Msg 257, Level 16, State 1: Line 1: Implicit conversion from datatype ‘CHAR’ to ‘INT’ is not allowed. Use the CONVERT function to run this query.
The check constraint was defined to expect the column to be a datatype int, but the data being inserted is a datatype char.
Furthermore, you cannot insert the data as type int because column now uses a datatype of char. The insert returns this message:
Msg 257, Level 16, State 1: Line 1: Implicit conversion from datatype ‘INT’ to ‘CHAR’ is not allowed. Use the CONVERT function to run this query.
The modify operation is very flexible, but must be used with caution. In general, modifying to an implicitly convertible datatype works without errors. Modifying to an explicitly convertible datatype may lead to inconsistencies in the tables schema. Use sp_depends to identify all column-level dependencies before modifying a column’s datatype.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
