ALLBASE/SQL Reference Manual (36216-90216)

306 Chapter10
SQL Statements A - D
ALTER TABLE
Authorization
You can issue this statement if you have ALTER or OWNER authority for the table or if
you have DBA authority.
To define added referential constraints, the table owner must have REFERENCES
authority on the referenced table and referenced columns, own the referenced table, or
have DBA authority.
To specify a DBEFileSetName for a long column, the table owner must have TABLESPACE
authority on the referenced DBEFileSet.
To specify a
DBEFileSetName for a check constraint, the section owner must have
SECTIONSPACE authority on the referenced DBEFileSet.
Examples
Two new columns, ShippingWeight and PartDescription, are added to table
PurchDB.Parts. ShippingWeight must be greater than 0.
ALTER TABLE PurchDB.Parts
ADD (ShippingWeight DECIMAL(6,3) CHECK (ShippingWeight > 0)
CONSTRAINT Check_Weight,
PartDescription CHAR(40))
A constraint is added to table PurchDB.Parts to ensure that the sales price is greater than
$100.
ALTER TABLE PurchDB.Parts
ADD CONSTRAINT CHECK (SalesPrice > 100.) CONSTRAINT Check_Price
A column named DiscountPercent is added to table PurchDB.OrderItems, with a default
value of 0 percent.
ALTER TABLE PurchDB.OrderItems
ADD (DiscountPercent FLOAT DEFAULT 0)
The constraint named Check_Price is dropped.
ALTER TABLE PurchDB.Parts
DROP CONSTRAINT Check_Price
The type of a table is changed:
ALTER TABLE PurchDB.OrderItems
SET TYPE PUBLICROW
The table's partition is modified to be partition PartsPart2.
ALTER TABLE PurchDB.Parts
SET PARTITION PartsPart2;
No more audit logging will be done on the table.
ALTER TABLE PurchDB.Parts
SET PARTITION NONE;