CREATE SCHEMA

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:
- 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
 
   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