SQL Reference

CREATE SCHEMA

The CREATE SCHEMA statement defines a schema. It is also possible to create some objects and grant privileges on objects within the statement.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

An authorization ID that holds SYSADM or DBADM authority can create a schema with any valid schema-name or authorization-name.

An authorization ID that does not hold SYSADM or DBADM authority can only create a schema with a schema-name or authorization-name that matches the authorization ID of the statement.

If the statement includes any schema-SQL-statements the privileges held by the authorization-name (if not specified, it defaults to the authorization ID of the statement) must include at least one of the following:

• The privileges required to perform each of the schema-SQL-statements
• SYSADM or DBADM authority.

Syntax

>>-CREATE SCHEMA------------------------------------------------>
 
>-----+-schema-name-------------------------------------+------->
      +-AUTHORIZATION--authorization-name---------------+
      '-schema-name--AUTHORIZATION--authorization-name--'
 
>-----+------------------------------+-------------------------><
      |  .------------------------.  |
      |  V                        |  |
      '----schema-SQL-statement---+--'
 

Description

schema-name

Names the schema. The name must not identify a schema already described in the catalog (SQLSTATE 42710). The name cannot begin with "SYS" (SQLSTATE 42939). The owner of the schema is the authorization ID that issued the statement.

AUTHORIZATION authorization-name

Identifies the user that is the owner of the schema. The value authorization-name is also used to name the schema. The authorization-name must not identify a schema already described in the catalog (SQLSTATE 42710).

schema-name AUTHORIZATION authorization-name

Identifies a schema called schema-name with the user called authorization-name as the schema owner. The schema-name must not identify a schema-name for a schema already described in the catalog (SQLSTATE 42710). The schema-name cannot begin with "SYS" (SQLSTATE 42939).

schema-SQL-statement

SQL statements that can be included as part of the CREATE SCHEMA statement are:

• CREATE TABLE statement excluding typed tables and summary tables (see CREATE TABLE)
• CREATE VIEW statement excluding typed views (see CREATE VIEW)
• CREATE INDEX statement (see CREATE INDEX)
• COMMENT ON statement (see COMMENT ON)
• GRANT statement (see GRANT (Table, View, or Nickname Privileges)).

Notes

• The owner of the schema is determined as follows:
  • If an AUTHORIZATION clause is specified, the specified authorization-name is the schema owner
  • If an AUTHORIZATION clause is not specified, the authorization ID that issued the CREATE SCHEMA statement is the schema owner.
• The schema owner is assumed to be a user (not a group).
• When the schema is explicitly created with the CREATE SCHEMA statement, the schema owner is granted CREATEIN, DROPIN, and ALTERIN privileges on the schema with the ability to grant these privileges to other users.
• The definer of any object created as part of the CREATE SCHEMA statement is the schema owner. The schema owner is also the grantor for any privileges granted as part of the CREATE SCHEMA statement.
• Unqualified object names in any SQL statement within the CREATE SCHEMA statement are implicitly qualified by the name of the created schema.
• If the CREATE statement contains a qualified name for the object being created, the schema name specified in the qualified name must be the same as the name of the schema being created (SQLSTATE 42875). Any other objects referenced within the statements may be qualified with any valid schema name.
• If the AUTHORIZATION clause is specified and DCE authentication is used, the group membership of the authorization-name specified will not be considered in evaluating the authorizations required to perform the statements that follow the clause. If the authorization-name specified is different than the authorization id creating the schema, an authorization failure may result during the execution of the CREATE SCHEMA statement.
• It is recommended not to use "SESSION" as a schema name. Since declared temporary tables must be qualified by "SESSION", it is possible to have an application declare a temporary table with a name identical to that of a persistent table. An SQL statement that references a table with the schema name "SESSION" will resolve (at statement compile time) to the declared temporary table rather than a persistent table with the same name. Since an SQL statement is compiled at different times for static embedded and dynamic embedded SQL statements, the results depend on when the declared temporary table is defined. If persistent tables, views or aliases are not defined with a schema name of "SESSION", these issues do not require consideration.

Examples

Example 1:  As a user with DBADM authority, create a schema called RICK with the user RICK as the owner.

  CREATE SCHEMA RICK AUTHORIZATION RICK

Example 2:  Create a schema that has an inventory part table and an index over the part number. Give authority on the table to user JONES.

  CREATE SCHEMA INVENTRY
 
     CREATE TABLE PART (PARTNO   SMALLINT NOT NULL,
                        DESCR    VARCHAR(24),
                        QUANTITY INTEGER)
 
     CREATE INDEX PARTIND ON PART (PARTNO)
 
     GRANT ALL ON PART TO JONES
 

Example 3:  Create a schema called PERS with two tables that each have a foreign key that references the other table. This is an example of a feature of the CREATE SCHEMA statement that allows such a pair of tables to be created without the use of the ALTER TABLE statement.

  CREATE SCHEMA PERS
 
     CREATE TABLE ORG (DEPTNUMB  SMALLINT NOT NULL,
                        DEPTNAME VARCHAR(14),
                        MANAGER  SMALLINT,
                        DIVISION VARCHAR(10),
                        LOCATION VARCHAR(13),
                        CONSTRAINT PKEYDNO
                           PRIMARY KEY (DEPTNUMB),
                        CONSTRAINT FKEYMGR
                           FOREIGN KEY (MANAGER)
                           REFERENCES STAFF (ID) )
 
     CREATE TABLE STAFF (ID        SMALLINT NOT NULL,
                          NAME     VARCHAR(9),
                          DEPT     SMALLINT,
                          JOB      VARCHAR(5),
                          YEARS    SMALLINT,
                          SALARY   DECIMAL(7,2),
                          COMM     DECIMAL(7,2),
                          CONSTRAINT PKEYID
                             PRIMARY KEY (ID),
                          CONSTRAINT FKEYDNO
                             FOREIGN KEY (DEPT)
                             REFERENCES ORG (DEPTNUMB) )