The CREATE SCHEMA statement creates a schema (logical name-space) in a database. If an AUTHORIZATION clause is included, the user name specified becomes the owner of the schema. If an AUTHORIZATION clause is not included, or if the AUTHORIZATION clause specifies the issuer of the CREATE SCHEMA statement, the issuer becomes the owner of the new schema. The owner of the schema also owns all database objects created in the context of the schema. Tables, views, and domains may be created as part of a CREATE SCHEMA statement; GRANT statements can also be included. Note that if any of the object definitions contained within the CREATE SCHEMA statement should fail, the entire command is rolled back.
No special privileges are required to execute the CREATE SCHEMA command.
The schema name argument is optional if the AUTHORIZATION user name argument is specified. If the schema name argument is not included, a new schema will be created with a name that matches the user name argument following the AUTHORIZATION keyword. The schema name must be unique within the database. It is strongly recommended that none of the SAND CDBMS SQL keywords be used as a schema name, as this may cause problems when referencing the schema in certain SQL statements.
AUTHORIZATION user name
The user name argument refers to an existing database user authorization identifier: this user becomes the owner of the new schema. If this clause is not included, the authorization who is executing the CREATE SCHEMA command becomes the owner of the new schema by default.
Multiple object definitions and/or GRANT statements can be included in a CREATE SCHEMA statement. Each statement must be separated from others by blank space; they should not be terminated individually with semicolons (;).
Each domain, table, or view defined in the CREATE SCHEMA statement implicitly belongs to the schema being created by the CREATE SCHEMA statement, and will be owned by the user executing the CREATE SCHEMA statement, or (if applicable) the user name specified in the AUTHORIZATION clause.
The CREATE TABLE and CREATE VIEW statements in a CREATE SCHEMA statement can reference other database objects, defined earlier or later in the same statement.
All database object references in the CREATE SCHEMA statement are in the context of the new schema. Therefore if any CREATE or GRANT clause references an object (for example, a table) that belongs to another schema, it must be qualified with the schema name.
If any one of the object definitions included within the CREATE SCHEMA statement fails to complete successfully, the entire statement is rolled back.
This example creates a schema named s2:
CREATE SCHEMA s2;
This example creates a schema named s3 and assigns it to user u2:
CREATE SCHEMA s3 AUTHORIZATION u2;
This example creates a schema named s4, containing table t1, with column c1 (having the INTEGER data type) and assigns it to user u3:
CREATE SCHEMA s4 AUTHORIZATION u3 CREATE TABLE t1 (c1 INTEGER);