Syntax
CREATE SCHEMA { [ schemaName AUTHORIZATION user-name ] | [ schemaName ] |
[ AUTHORIZATION user-name ] }
The CREATE SCHEMA statement is used to create a schema. A
schema name cannot exceed 128 characters. Schema names must be unique within
the database.
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 user-name
with a user name other than the current user name. See
"
derby.database.sqlAuthorization" for information about the
derby.database.sqlAuthorization property.
Note: Although the SQL standard allows you to specify any
AuthorizationIdentifier
as an AUTHORIZATION argument,
Derby 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))
