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 { [ 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.

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))