CREATE SCHEMA statement
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 only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
Authorization
An authorization ID that holds DBADM authority can create a schema with any valid schema-name or authorization-name.
An authorization ID that does not hold 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 a schema-SQL-statement, the privileges held
by the authorization-name (which, if not specified, defaults to the
authorization ID of the statement) must include at least one of the following authorities:
- The privileges required to perform each schema-SQL-statement
- DBADM authority
Syntax
Description
- schema-name
- An identifier that 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 who is the owner of the schema. The value of 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, whose owner is authorization-name. The schema-name must not identify a schema already described in the catalog (SQLSTATE 42710). The schema-name cannot begin with 'SYS' (SQLSTATE 42939).
- DATA CAPTURE
- Indicates
whether extra information for data replication is to be written to the log. The default is determined based on the value of database configuration
parameter dft_schemas_dcc. If the value is
Yes
the default is CHANGES, otherwise the default is NONE.- NONE
- Indicates that no extra information for data replication will be logged.
- CHANGES
- Indicates that extra information regarding SQL changes to this schema will be written to the log. This option is required if this schema will be replicated and a replication capture program is used to capture changes for this schema from the log.
- schema-SQL-statement
- SQL statements that can be included as part of the CREATE SCHEMA statement are:
- CREATE TABLE statement, excluding typed tables and materialized query tables
- CREATE VIEW statement, excluding typed views
- CREATE INDEX statement
- COMMENT statement
- GRANT statement
- ENABLE ROW MODIFICATION TRACKING
- Indicates tables created in the schema are to be enabled for logical backup. Applies only to columnar organized tables. For a list of restrictions, see Schema enabled for row modification tracking.
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.
- Schema names that are shorter than 8-bytes are padded with blanks and stored in the catalog as 8-byte names.
- 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.
- 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.
- Setting the DATA CAPTURE attribute at the schema level causes newly created tables to inherit the DATA CAPTURE attribute from the schema if one is not specified at the table level.
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) )