CREATE SCHEMA

Creates a schema that can be thought of as a conceptual object containing definitions of tables, views, and permissions.

Syntax

CREATE SCHEMA AUTHORIZATION owner
    [ < schema_element > [ ...n ] ]

< schema_element > ::=
    { table_definition | view_definition | grant_statement }

Arguments

AUTHORIZATION owner

Specifies the ID of the schema object owner. This identifier must be a valid security account in the database.

table_definition

Specifies a CREATE TABLE statement that creates a table within the schema.

view_definition

Specifies a CREATE VIEW statement that creates a view within the schema.

grant_statement

Specifies a GRANT statement that grants permissions for a user or a group of users.

Remarks

CREATE SCHEMA provides a way to create tables and views and to grant permissions for objects with a single statement. If errors occur when creating any objects or granting any permissions specified in a CREATE SCHEMA statement, none of the objects are created.

The created objects do not have to appear in logical order, except for views that reference other views. For example, a GRANT statement can grant permission for an object before the object itself is created, or a CREATE VIEW statement can appear before the CREATE TABLE statements creating the tables referenced by the view. Also, CREATE TABLE statements can declare foreign keys to tables specified later. The exception is that if the select from one view references another view, the referenced view must be specified before the view that references it.

Permissions

CREATE SCHEMA permissions default to all users, but they must have permissions to create the objects that participate in the schema.

Examples

A. Grant access to objects before object creation

This example shows permissions granted before the objects are created.

CREATE SCHEMA AUTHORIZATION ross 
GRANT SELECT on v1 TO public
CREATE VIEW v1(c1) AS SELECT c1 from t1
CREATE TABLE t1(c1 int)

B. Create mutually dependent FOREIGN KEY constraints

This example creates mutually dependent FOREIGN KEY constraints. Other methods would take several steps to accomplish what is enabled by this CREATE SCHEMA example.

CREATE SCHEMA AUTHORIZATION ross
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT REFERENCES t2(c1))
CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 INT REFERENCES t1(c1))