ALLBASE/SQL Reference Manual (36216-90216)
362 Chapter10
SQL Statements A - D
CREATE TABLE
• If no partition is specified, the table is placed in the DEFAULT partition.
• To specify that a table is in no partition, the partition NONE can be specified.
• Partitions can be created and tables placed in them without DATA audit logging being
enabled for a DBEnvironment. However, the partition information is only used in audit
log records. Thus, partition information is not utilized until the DBEnvironment has
DATA audit logging enabled through the START DBE NEWLOG statement.
• If the IN
DBEFileSetName1
clause is specified for the table or the IN
DBEFileSetName2
clause is specified for a long column, but the table owner does not
have TABLESPACE authority for the specified DBEFileSet, a warning is issued and the
default TABLESPACE DBEFileSet is used instead. (Refer to syntax for the GRANT
statement and the SET DEFAULT DBEFILESET statement.)
• If the IN
DBEFileSetName3
clause is specified for a check constraint, but the table
owner does not have SECTIONSPACE authority for the specified DBEFileSet, a
warning is issued and the default SECTIONSPACE DBEFileSet is used instead. (Refer
to syntax for the GRANT statement and the SET DEFAULT DBEFILESET statement.)
Authorization
You must have RESOURCE or DBA authority to use this statement. To define referential
constraints, the table owner must have REFERENCES authority on the referenced table
and referenced columns, own the referenced table, or have DBA authority for the life of the
referential constraint. The REVOKE, DROP GROUP, and REMOVE FROM GROUP statements are
not permitted if they remove REFERENCES authority from the table's owner until the
referential constraint or table is dropped or ownership is transferred to someone else.
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
1. Creating and accessing tables
This public table is accessible to any user or program that can start a DBE session. It is
also accessible by concurrent transactions.
CREATE PUBLIC TABLE PurchDB.SupplyPrice
(PartNumber CHAR(16) NOT NULL,
VendorNumber INTEGER NOT NULL,
VendPartNumber CHAR(16) NOT NULL,
UnitPrice DECIMAL(10,2),
DeliveryDays SMALLINT DEFAULT 0,
DiscountQty SMALLINT)
IN PARTITION PartsPart
IN PurchFS;
REVOKE ALL PRIVILEGES ON PurchDB.SupplyPrice FROM PUBLIC
GRANT SELECT,UPDATE ON Purch.DB.SupplyPrice TO Accounting