Creates a new schema in the current default catalog of a
multischema database.
NOTE
Use of the CREATE SCHEMA statement to create a database is
a deprecated feature. If you specify physical attributes of
a database such as the root file parameters, you receive an
informational message, but SQL creates the database anyway.
SQL> CREATE SCHEMA FILENAME TEST SNAPSHOT IS DISABLED;
%SQL-I-DEPR_FEATURE, Deprecated Feature: SCHEMA (meaning DATABASE)
If you do not specify any physical attributes, you may
receive an error message noting that you must enable
multischema naming.
SQL> CREATE SCHEMA PARTS
%SQL-F-SCHCATMULTI, Schemas and catalogs may only be referenced with
multischema enabled
A schema is a group of definitions within a database. The CREATE
SCHEMA statement lets you specify in a single SQL statement all
data and privilege definitions for a new schema. You can also add
definitions to the schema later.
A database, in addition to schema definitions, includes database
system files and user data. If you need to specify any physical
database characteristics such as the database root file or
storage area parameters, use the CREATE DATABASE statement. See
the CREATE DATABASE statement for more information.
You can specify any number of optional schema elements to the
CREATE SCHEMA statement. Schema elements are any of the CREATE
statements (except CREATE STORAGE AREA, CREATE DOMAIN . . . FROM
path-name, and CREATE TABLE . . . FROM path-name) or a GRANT
statement.
These statements require statement terminators, except when they
are part of a CREATE SCHEMA or CREATE DATABASE statement. When
you use these statements within a CREATE SCHEMA statement, use a
statement terminator on the last schema element only. The first
statement terminator that SQL encounters ends the CREATE SCHEMA
statement. Later CREATE or GRANT statements are not within the
scope of the CREATE SCHEMA statement.
1 – Environment
You can use the CREATE SCHEMA statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
CREATE SCHEMA -+-> <schema-name> -------------------------+-+
+-> AUTHORIZATION <auth-id> ---------------+ |
+-> <schema-name> AUTHORIZATION <auth-id> -+ |
+--------------------------------------------+
+-+-------------------------+--------------->
+-+--> schema-element --+-+
+----------<----------+
schema-name =
--+---------------------------------------------+
+------> <catalog-name> --------------+->. ---+
+-> " -> <alias>.<catalog-name> ->" --+ |
| +-----------------------------------------+
| +--------------> <name-of-schema> ------+->
+-> " -> <alias>.<name-of-schema> ->" ------+
schema-element =
-+-> create-collating-sequence-statement -+->
+-> create-domain-statement -------------+
+-> create-index-statement --------------+
+-> create-sequence-statement -----------+
+-> create-storage-map-statement --------+
+-> create-table-statement --------------+
+-> create-trigger-statement ------------+
+-> create-view-statement ---------------+
+-> grant-statement ---------------------+
3 – Arguments
3.1 – AUTHORIZATION auth id
If you do not specify a schema name, the authorization identifier
specifies the default schema.
If you want to comply with the ANSI/ISO 1989 standard, specify
the AUTHORIZATION clause without the schema name. Specify both
the AUTHORIZATION clause and the schema name to comply with the
current ANSI/ISO SQL standard.
3.2 – create-collating-sequence-statement
See the CREATE COLLATING_SEQUENCE statement for details.
If you want to specify a collating sequence in a CREATE DOMAIN
statement embedded in a CREATE SCHEMA statement, you must first
specify a CREATE COLLATING SEQUENCE statement within the same
CREATE SCHEMA statement.
3.3 – create-domain-statement
See the CREATE DOMAIN statement for details.
You cannot use the FROM path-name clause when embedding a CREATE
DOMAIN statement in a CREATE SCHEMA statement. You can, however,
issue a separate CREATE DOMAIN statement following the CREATE
SCHEMA statement. You can also describe the domain directly
within the CREATE SCHEMA statement.
If you want to specify a collating sequence in your embedded
CREATE DOMAIN statement, you must first specify a CREATE
COLLATING SEQUENCE statement within the same CREATE SCHEMA
statement.
3.4 – create-function-statement
See the CREATE Routine statement for details.
3.5 – create-index-statement
See the CREATE INDEX statement for details.
3.6 – create-module-statement
See the CREATE MODULE statement for details.
3.7 – create-procedure-statement
See the CREATE Routine statement for details.
3.8 – create-sequence-statement
See the CREATE SEQUENCE statement for details.
3.9 – create-storage-map-statement
See the CREATE STORAGE_MAP statement for details.
3.10 – create-table-statement
See the CREATE TABLE statement for details.
You cannot use the FROM path-name clause when embedding a CREATE
TABLE statement in a CREATE SCHEMA statement. You can, however,
issue a separate CREATE TABLE statement following the CREATE
SCHEMA statement. You can also describe the table directly within
the CREATE SCHEMA statement.
The CREATE TABLE statements in a CREATE SCHEMA statement can
refer to domains not yet created, provided that CREATE DOMAIN
statements for the domains are in the same CREATE SCHEMA
statement.
3.11 – create-trigger-statement
See the CREATE TRIGGER statement for details.
3.12 – create-view-statement
See the CREATE VIEW statement for details.
3.13 – grant-statement
See the GRANT statement for details.
3.14 – schema-element
Some CREATE statements or a GRANT statement.
3.15 – schema-name
Specifies the name of the schema created by the CREATE SCHEMA
statement.
You can qualify the schema name with either a catalog name or the
catalog name qualified by the alias. You must enclose the alias
and catalog name in double quotation marks and separate them with
a period. You must issue the SET QUOTING RULES statement before
you specify the alias and catalog name pair, or SQL issues an
error message about the use of double quotation marks.
For information on qualifying schema names with aliases and
catalog names, see the User_Supplied_Names HELP topic.
4 – Example
Example 1: Creating a schema within a multischema database
The following interactive statements create a database that
contains a schema within a catalog. You issue the CREATE SCHEMA
statement alone or as part of a CREATE DATABASE statement.
SQL> SET DIALECT 'SQL99';
SQL> CREATE DATABASE ALIAS PERS_ALIAS FILENAME personnel MULTISCHEMA IS ON;
SQL> CREATE CATALOG "PERS_ALIAS.ADMIN";
SQL> CREATE SCHEMA "PERS_ALIAS.ADMIN".PAYROLL;
SQL> SHOW SCHEMAS;
Schemas in database PERS_ALIAS
"PERS_ALIAS.ADMIN".PAYROLL
"PERS_ALIAS.RDB$CATALOG".RDB$SCHEMA