|
|
The CREATE SCHEMA statement creates a schema
and associates an authorization name with it. The schema defines
a database containing tables, views, indexes, procedures, rules, and
authorization groups with the same owner name. Entries are created
in the system catalog views upon completion of the execution of
this statement.
ISQL or Application Programs
CREATE SCHEMA AUTHORIZATION AuthorizationName
[TableDefinition
ViewDefinition
IndexDefinition
ProcedureDefinition
RuleDefinition
CreateGroup
AddToGroup
GrantStatement ][...]
- AuthorizationName
specifies the owner of the database objects.
If you have RESOURCE authority, the AuthorizationName must be your
DBEUserID, a class name, or an authorization group name to which you
belong. You cannot specify a different owner for the objects you create.
If you have DBA authority, the AuthorizationName can be any DBEUserID,
class name, or authorization group name. The owner of the objects you
create does not have to match the AuthorizationName if the owner has DBA
authority.
You must specify an AuthorizationName; there is no default.
- TableDefinition
defines a table and automatic locking strategy. For complete syntax,
refer to the CREATE TABLE syntax.
- ViewDefinition
defines a view of a table, another view, or a combination of tables
and views. For complete syntax, refer to the CREATE VIEW
syntax.
- IndexDefinition
creates an index on one or more columns. For complete
syntax, refer to the CREATE INDEX syntax.
- ProcedureDefinition
creates a procedure which defines a sequence of SQL statements. For
correct syntax, refer to the CREATE PROCEDURE syntax.
- RuleDefinition
creates a rule to fire a stored procedure. For complete
syntax, refer to the CREATE RULE syntax.
- CreateGroup
defines an authorization group. For complete syntax,
refer to the CREATE GROUP syntax.
- 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.
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.
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.
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
CREATE PUBLIC TABLE Events
(SponsorClub CHAR(15),
Event CHAR(30),
Date DATE DEFAULT CURRENT_DATE,
Time TIME,
Coordinator CHAR(20),
FOREIGN KEY (Coordinator, SponsorClub)
REFERENCES Members (MemberName, Club)
CONSTRAINT Events_FK)
IN RecFS
|