 |
» |
|
|
|
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. Scope |  |
ISQL or Application Programs SQL Syntax |  |
CREATE SCHEMA AUTHORIZATION AuthorizationName [ TableDefinition ViewDefinition IndexDefinition ProcedureDefinition RuleDefinition CreateGroup AddToGroup GrantStatement ] [...] Parameters |  |
- 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.
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:
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
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
|
|