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
>>-CREATE SCHEMA------------------------------------------------>
>--+-schema-name------------------------------------+----------->
+-AUTHORIZATION--authorization-name--------------+
'-schema-name--AUTHORIZATION--authorization-name-'
>--+---------------------------+--+--------------------------+-><
'-DATA CAPTURE--+-NONE----+-' | .----------------------. |
'-CHANGES-' | V | |
'---schema-SQL-statement-+-'
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
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.
- 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) )