CREATE SCHEMA examples
Derby Reference Manual
37
CREATE SCHEMA statement
A schema is a way to logically group objects in a single collection and provide a unique
namespace for objects.
Syntax
CREATE SCHEMA { [
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
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
"
" for information about the
derby.database.sqlAuthorization
property.
Note: Although the SQL standard allows you to specify any
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))
CREATE SEQUENCE statement
The CREATE SEQUENCE statement creates a sequence generator, which is a
mechanism for generating exact numeric values, one at a time.
The owner of the schema where the sequence generator lives automatically gains the
USAGE privilege on the sequence generator, and can grant this privilege to other users
and roles. Only the
and the owner of the sequence generator can grant
