User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

Creates a collection of tables, views, and permissions for a database user.

Syntax

     CREATE SCHEMA AUTHORIZATIONuserid
          ...
          [
              create-table-statement
              | create-view-statement
              | grant-statement
          ],...

Usage

Anywhere

Transact-SQL Usage

You cannot use the CREATE SCHEMA statement in a Transact-SQL batch or procedure.

Permissions

Must have RESOURCE authority.

Side effects

Automatic commit.

See also

• CREATE TABLE statement
• CREATE VIEW statement
• GRANT statement

Description

The CREATE SCHEMA statement creates a schema. A schema is a collection of tables, views, and their associated permissions.

The userid must be the user ID of the current connection. You cannot create a schema for another user.

If any of the statements contained in the CREATE SCHEMA statement fails, then the entire CREATE SCHEMA statement is rolled back.

The CREATE SCHEMA statement is simply a way of collecting together individual CREATE and GRANT statements into one operation. There is no SCHEMA database object created in the database, and to drop the objects you must use individual DROP TABLE or DROP VIEW statements. To revoke permissions, you must use a REVOKE statement for each permission granted.

The individual CREATE or GRANT statements are not separated by statement delimiters. The statement delimiter marks the end of the CREATE SCHEMA statement itself.

The individual CREATE or GRANT statements must be ordered such that the objects are created before permissions are granted on them.

Although the CREATE SCHEMA statement can currently be executed by a user more than once, this is not recommended, and may not be supported in future releases.

Examples

The following CREATE SCHEMA statement creates a schema consisting of two tables. The statement must be executed by the user ID sample_user, who must have RESOURCE authority. If the statement creating table t2 fails, neither table is created.

     CREATE SCHEMA AUTHORIZATION sample_user
     CREATE TABLE t1 ( id1 INT PRIMARY KEY )
     CREATE TABLE t2 ( id2 INT PRIMARY KEY ) ;

The statement delimiter in the following CREATE SCHEMA statement is placed after the first CREATE TABLE statement. As the statement delimiter marks the end of the CREATE SCHEMA statement, the example is interpreted as a two statement batch by the database engine. Consequently, if the statement creating table t2 fails, the table t1is still created.

     CREATE SCHEMA AUTHORIZATION sample_user
     CREATE TABLE t1 ( id1 INT PRIMARY KEY ) ;
     CREATE TABLE t2 ( id2 INT PRIMARY KEY ) ;