CREATE SCHEMA statement The CREATE SCHEMA statement creates a schema, which is a way to logically group objects in a single collection and to provide a unique namespace for objects. CREATE SCHEMA statementSQL statementsCREATE SCHEMAschemascreating Syntax CREATE SCHEMA { [ schemaName AUTHORIZATION userName ] | [ schemaName ] | [ AUTHORIZATION userName ] }

A schema name cannot exceed 128 characters. Schema names must be unique within the database.

A schema name cannot start with the prefix SYS (after case normalization). Use of the prefix SYS raises a SQLException.

The CREATE SCHEMA statement is subject to access control when the derby.database.sqlAuthorization property is set to true for the database or system. Only the database owner can create a schema with a name different from the current user name, and only the the database owner can specify AUTHORIZATION userName with a user name other than the current user name.

Although the SQL standard allows you to specify any authorizationIdentifier as an AUTHORIZATION argument, allows you to specify only a user, not a role. CREATE SCHEMA examples

To create a schema for airline-related tables and give the authorization ID anita access to all of the objects that use the schema, use the following syntax:CREATE SCHEMA FLIGHTS AUTHORIZATION anita

To create a schema employee-related tables, use the following syntax:CREATE SCHEMA EMP

To create a schema that uses the same name as the authorization ID takumi, use the following syntax:CREATE SCHEMA AUTHORIZATION takumi

To create a table called availability in the EMP and FLIGHTS schemas, use the following syntax:CREATE TABLE FLIGHTS.AVAILABILITY (FLIGHT_ID CHAR(6) NOT NULL, SEGMENT_NUMBER INT NOT NULL, FLIGHT_DATE DATE NOT NULL, ECONOMY_SEATS_TAKEN INT, BUSINESS_SEATS_TAKEN INT, FIRSTCLASS_SEATS_TAKEN INT, CONSTRAINT FLT_AVAIL_PK PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER, FLIGHT_DATE))

CREATE TABLE EMP.AVAILABILITY (HOTEL_ID INT NOT NULL, BOOKING_DATE DATE NOT NULL, ROOMS_TAKEN INT, CONSTRAINT HOTELAVAIL_PK PRIMARY KEY (HOTEL_ID, BOOKING_DATE))