Chapter 9 SQL Statements
395
If PRIMARY KEY is specified, the table must not already have a primary
key that was created by the CREATE TABLE statement or another
ALTER TABLE statement.
MODIFY column-definition Change the length or data type of an existing
column in a table. If NOT NULL is specified, a NOT NULL constraint is
added to the named column. Otherwise, the NOT NULL constraint for the
column will not be changed. If necessary, the data in the modified column
will be converted to the new data type. If a conversion error occurs, the
operation will fail and the table will be left unchanged.
You cannot modify a column to make it a computed column. Computed
columns can only be added or dropped.
Deleting an index, constraint, or key
If the column is contained in a uniqueness constraint, a foreign key, or a
primary key, then the constraint or key must be deleted before the column
can be modified. If a primary key is deleted, all foreign keys referencing
the table will also be deleted.
You cannot MODIFY a table or column constraint. To change a
constraint, you must DELETE the old constraint and ADD the new
constraint.
MODIFY column-name DEFAULT default-value Change the default
value of an existing column in a table. To remove a default value for a
column, specify DEFAULT NULL. Modifying a default value does not
change any existing values in the table.
ALTER column-name SET DEFAULT default-value Change the
default value of an existing column in a table. You can also use the MODIFY
clause for this task, but ALTER is SQL/92 compliant, and MODIFY is not.
Modifying a default value does not change any existing values in the table.
ALTER column-name DROP DEFAULT Remove the default value of
an existing column in a table. You can also use the MODIFY clause for this
task, but ALTER is SQL/92 compliant, and MODIFY is not. Dropping a
default does not change any existing values in the table.
ALTER column-name SET COMPUTE (expression) Change the
expression associated with a computed column. The values in the column are
recalculated when the statement is executed, and the statement fails if the
new expression is invalid.
ALTER column-name DROP COMPUTE Change a column from being a
computed column to being a non-computed column. This statement does not
change any existing values in the table.
To Next Page
Loading...