O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

CREATE SCHEMA

Synopsis

This statement creates a schema — i.e., a named group of related objects. A schema is a collection of tables, views, and their associated permissions. The schema is associated with an existing, valid user ID (called the owner ).

Vendor

Command

SQL Server

Supported

MySQL

Not supported

Oracle

Supported, with variations

PostgreSQL

Not supported

SQL99 Syntax and Description

CREATE SCHEMA [schema_name] [AUTHORIZATION owner_name]
[DEFAULT CHARACTER SET char_set_name]
[PATH schema_name [,...n] ]

   [ <create_table_statement1> [...n] ]
   [ <create_view_statement1>  [...n] ]
   [ <grant statement1> [...n] ]

The CREATE SCHEMA statement is a container that can hold many other CREATE and GRANT statements. As an option, a DEFAULT CHARACTER SET names the schema’s default character set. The PATH also may be declared for any objects in the schema that reside on the filesystem.

Microsoft SQL Server and Oracle Syntax

CREATE SCHEMA AUTHORIZATION owner_name
   [ <create_table_statement1> [...n] ]
   [ <create_view_statement1>  [...n] ]
   [ <grant statement1> [...n] ]

If any statement fails within the CREATE SCHEMA statement, then the entire statement fails. One good thing about CREATE SCHEMA is that the objects within do not need to be organized according to any dependency. For example, a GRANT statement normally could not be issued for a table that does not exist yet. However, all the GRANT statements first could be placed in the CREATE SCHEMA statement, followed by the CREATE statements ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required