The CREATE SCHEMA statement creates a named collection of database objects (tables, views and privileges). The schema_name parameter must be the same as the effective user for the session issuing the create schema statement. All objects specified in the CREATE SCHEMA statement are owned by that user. You cannot create a schema for another user. Each user has one schema per database.

The statements in the CREATE SCHEMA statement must not be separated by semicolon delimiters. However, the CREATE SCHEMA statement must be terminated with a semicolon following the last object definition statement (CREATE TABLE, CREATE VIEW, or GRANT).

If object definitions are omitted, an empty schema is created. For details about the statements used to create tables and privileges, see Create Table (extended) (see page Create Table (extended)) and Grant (see page Grant) respectively. If an error occurs within the CREATE SCHEMA statement, the entire statement is rolled back. If you issue a CREATE SCHEMA specifying an existing schema (schema_name), OpenSQL issues an error.

To add objects to your schema, issue the required create statements outside of a CREATE SCHEMA statement. If no schema exists for your user identifier, one is implicitly created when you create any database object. Thereafter, if you issue a CREATE SCHEMA statement, OpenSQL issues an error.

If, within a CREATE SCHEMA statement, you create tables that have referential constraints, the order of CREATE TABLE statements is not significant. This is unlike the requirements for creating tables with referential constraints outside of a CREATE SCHEMA statement, where the referenced table must exist before a constraint that references it can be created. For details about referential constraints, see Create Table (extended) (see page Create Table (extended)) in this chapter.

Other users can reference objects in your schema if you have granted them the required permissions. To reference an object in a schema other than your own, specify the object name as follows:

schema.object

For example, user harry can select data from user joe's employees table (if joe has granted harry select permission). Harry can issue the following SELECT statement: