ALLBASE/SQL Reference Manual (36216-90216)

304 Chapter10
SQL Statements A - D
ALTER TABLE
SQL Syntax—SetPartitionSpecification
SET PARTITION { PartitionName
DEFAULT
NONE }
Parameters—SetPartitionSpecification
PartitionName specifies the new partition of the table.
DEFAULT specifies the new partition of the table to be the default
partition of the DBEnvironment. If the default partition
number is later changed, that change will automatically
be recorded the next time an INSERT, UPDATE,orDELETE
operation is executed on the table. If the default partition
is NONE at that time, audit logging of the operation is not
done.
NONE specifies that the table is no longer in any partition. No
further audit logging will be done on the table.
Description
• Unless the table is currently empty, you cannot specify the NOT NULL attribute for
any new columns unless you specify a default value.
• If no DEFAULT clause is given for an added column, an implicit DEFAULT NULL is
assumed. Any INSERT statement which does not include a column for which a default
has been declared causes the default value to be inserted into that column for all rows
inserted.
• All rows currently in the table are updated with the default value for any new column
which specifies default values.
• The ALTER TABLE statement can invalidate stored sections.
• Character strings are accepted as date/time default values.
• If an added constraint is violated when it is defined, an error message is immediately
issued and the ALTER TABLE statement has no effect.
• A unique constraint referenced by a FOREIGN KEY cannot be dropped without first
dropping the referential constraint.
• Constraints being added in AddConstraintSpecification must be on existing columns of
the table.
• The ALTER TABLE statement can be used to change the type of an existing table.
Changing the type of a table redefines the locking strategy that ALLBASE/SQL uses
when the table is accessed. You can decide whether to use page or row level locking for
your applications.
• No other transaction can access the table until the transaction that issued the ALTER
TABLE statement has committed.
• The type of a table is changed permanently when you issue a COMMIT WORK statement.