CREATE SCHEMA - Create schema

You use CREATE SCHEMA to create a schema. At the same time you can define tables, views, routines, privileges and indexes. You can also modify the schema later with the appropriate CREATE, ALTER and DROP statements.

The current authorization identifier must have the special privilege CREATE SCHEMA.

---

CREATE SCHEMA

     { schema [AUTHORIZATION authorization_identifier ] |

       AUTHORIZATION authorization_identifier }

     [ create_table_statement |

              create_view_statement |

              create_function_statement |

              create_procedure_statement |

              grant_statement |

              create_index_statement  ]  ...

---

schema

Name of the schema. The unqualified schema name must be unique within the database. You can also qualify the schema name with a database name.

schema omitted:
The name of the authorization identifier in the AUTHORIZATION clause is used as the schema name.

AUTHORIZATION authorization_identifier

The authorization identifier owns the schema.

This authorization identifier is used as the name of the schema if you do not specify a schema name.

AUTHORIZATION authorization_identifier omitted:
If an authorization identifier has been defined for the compilation unit, it owns the schema. Otherwise, the current authorization identifier becomes the owner.

create/grant_statements

If you use unqualified table, routine and index names in the CREATE and GRANT statements, the names are automatically qualified with the database and schema names of the schema.

create_table_statement

CREATE TABLE statement that creates a base table for the schema.

create_view_statement

CREATE VIEW statement that creates a view for the schema.

create_function_statement

CREATE FUNCTION statement that creates a UDF for the schema.

create_procedure_statement

CREATE PROCEDURE statement that creates a procedure for the schema.

grant_statement

GRANT statement that grants privileges for a base table, a view or a routine of this schema. You cannot grant special privileges with the GRANT statement.

create_index_statement

CREATE INDEX statement that creates and index for the schema.

create/grant_statements not specified:

An empty schema is created.

How CREATE SCHEMA functions

The CREATE TABLE, CREATE VIEW, CREATE FUNCTION, CREATE PROCEDURE, GRANT, and CREATE INDEX statements that are specified in the CREATE SCHEMA statement are executed in precisely the order in which they are specified. You must therefore place statements that reference existing tables, routines or views after the statement that creates these tables, routines or views.

Example

The example below creates the schema ADDONS and the table IMAGES.The privileges for the IMAGES table are assigned to the authorization identifier utiusr1.

   CREATE SCHEMA addons
   CREATE TABLE images OF BLOB
   (
    MIME ('image / gif'), 
    USAGE ('images for parts.item_cat.image'), 
    '<Photographer>Hans Sesamer</Photographer>'
   ) USING SPACE blobspace
   GRANT ALL PRIVILEGES ON images TO utiusr1

See also

CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE FUNCTION, CREATE PROCEDURE, GRANT, DROP SCHEMA