ALLBASE/SQL Reference Manual (36216-90216)

352 Chapter10
SQL Statements A - D
CREATE SCHEMA
AddToGroup adds one or more users, authorization groups, or combination of users and
authorization groups to an authorization group. For complete syntax, refer
to the ADD TO GROUP syntax.
GrantStatement specifies the type of authorities for a table, view, or module. For
complete syntax, refer to the GRANT syntax.
Description
• Note that a comma or semicolon is not allowed between the object definitions in the
CREATE SCHEMA syntax.
• You cannot use the following CREATE statements within the CREATE SCHEMA
statement:
• CREATE DBEFILE
• CREATE DBEFILESET
• You cannot use this statement to add to a schema that already exists. A schema for a
given authorization name exists if there are any objects (tables, views, indexes,
procedures, rules, or groups) owned by that authorization name.
• When the CREATE SCHEMA statement is part of a procedure, no
ProcedureDefinition may be included.
Authorization
You can execute this statement if you have RESOURCE authority or DBA authority. With
RESOURCE authority you can create a schema by using your own name or the
authorization group name to which you belong. If you have DBA authority, then you can
create a schema with any AuthorizationName.
Example
In the following example, RecDB is the AuthorizationName (owner name). All the tables
created here are owned by RecDB; it is not necessary to repeat the owner name for each
creation statement.
CREATE SCHEMA AUTHORIZATION RecDB
CREATE PUBLIC TABLE Clubs
(ClubName CHAR(15) NOT NULL
PRIMARY KEY CONSTRAINT Clubs_PK,
ClubPhone SMALLINT,
Activity CHAR(18))
IN RecFS
CREATE PUBLIC TABLE Members
(MemberName CHAR(20) NOT NULL,
Club CHAR(15) NOT NULL,
MemberPhone SMALLINT,
PRIMARY KEY (MemberName, Club) CONSTRAINT Members_PK,
FOREIGN KEY (Club) REFERENCES Clubs (ClubName)
CONSTRAINT Members_FK)
IN RecFS