CREATE SCHEMA v11

Suggest edits

Name

CREATE SCHEMA -- define a new schema.

Synopsis

CREATE SCHEMA AUTHORIZATION <username> <schema_element> [ ... ]

Description

This variation of the CREATE SCHEMA command creates a new schema owned by username and populated with one or more objects. The creation of the schema and objects occur within a single transaction so either all objects are created or none of them including the schema. (Please note: if you are using an Oracle database, no new schema is created – username, and therefore the schema, must pre-exist.)

A schema is essentially a namespace: it contains named objects (tables, views, etc.) whose names may duplicate those of other objects existing in other schemas. Named objects are accessed either by “qualifying” their names with the schema name as a prefix, or by setting a search path that includes the desired schema(s). Unqualified objects are created in the current schema (the one at the front of the search path, which can be determined with the function CURRENT_SCHEMA). (The search path concept and the CURRENT_SCHEMA function are not compatible with Oracle databases.)

CREATE SCHEMA includes subcommands to create objects within the schema. The subcommands are treated essentially the same as separate commands issued after creating the schema. All the created objects will be owned by the specified user.

Parameters

username

The name of the user who will own the new schema. The schema will be named the same as username. Only superusers may create schemas owned by users other than themselves. (Please note: In Advanced Server the role, username, must already exist, but the schema must not exist. In Oracle, the user (equivalently, the schema) must exist.)

schema_element

An SQL statement defining an object to be created within the schema. CREATE TABLE, CREATE VIEW, and GRANT are accepted as clauses within CREATE SCHEMA. Other kinds of objects may be created in separate commands after the schema is created.

Notes

To create a schema, the invoking user must have the CREATE privilege for the current database. (Of course, superusers bypass this check.)

In Advanced Server, there are other forms of the CREATE SCHEMA command that are not compatible with Oracle databases.

Examples

CREATE SCHEMA AUTHORIZATION enterprisedb
    CREATE TABLE empjobs (ename VARCHAR2(10), job VARCHAR2(9))
    CREATE VIEW managers AS SELECT ename FROM empjobs WHERE job = 'MANAGER'
    GRANT SELECT ON managers TO PUBLIC;
← Prev

CREATE ROLE

Next →

CREATE SEQUENCE