SQL Guide : solidDB® SQL statements : CREATE SCHEMA
  
CREATE SCHEMA
CREATE SCHEMA schema_name
Usage
The CREATE SCHEMA statement creates a new schema.
Schemas are a collection of database objects, such as tables, views, indexes, events, triggers, sequences, and stored procedures for a database user. The default schema name is the user id; there is one default schema for each user. The use of schemas in solidDB® conforms to the SQL standard.
The schema name is used to qualify a database object name. Database object names are qualified in all DML statements as:
catalog_name.schema_name.database_object_name
or
user_id.database_object_name
To logically partition a database, users can create a catalog before they create a schema. For details on creating a catalog, read CREATE CATALOG. When creating a new database or converting an old database to a new format, you are prompted for a default catalog name.
To use schemas, a schema name must be created before creating the database object name (such as a table name or procedure name). However, a database object name can be created without a schema name. In such cases, database objects are qualified using user_id only.
You can specify the database object names in a DML statement explicitly by fully qualifying them or implicitly by setting the schema name context using: SET SCHEMA schema_name
Creating a schema does not automatically make that schema the current default schema. If you have created a new schema and want your subsequent commands to execute within that schema, you must also execute the SET SCHEMA statement. For example:
CREATE SCHEMA MySchema;
CREATE TABLE t1; -- not in MySchema
SET SCHEMA MySchema;
CREATE TABLE t2; -- in MySchema
A schema can be dropped from a database using the DROP SCHEMA statement. When dropping a schema name, all objects associated with the schema name must be dropped prior to dropping the schema.
A schema context can be removed using the SET SCHEMA USER statement.
Rules for resolving schema names:
A fully qualified name (schema_name.database_object_name) does not need any name resolution, but will be validated.
If a schema context is not set using SET SCHEMA, all database object names are resolved always using the user id as the schema name.
If the database object name cannot be resolved from the schema name, then the database object name is resolved from all existing schema names.
If name resolution finds either zero matching or more than one matching database object name, then a solidDB® server issues a name resolution conflict error.
Examples
-- Assume the userID is SMITH.
CREATE SCHEMA FINANCE;
CREATE TABLE EMPLOYEE (EMP_ID INTEGER);
SET SCHEMA FINANCE;
CREATE TABLE EMPLOYEE (ID INTEGER);
SELECT ID FROM EMPLOYEE;
-- In this case, the table is qualified to FINANCE.EMPLOYEE
SELECT EMP_ID FROM EMPLOYEE;
-- This will give an error as the context is with FINANCE and
-- table is resolved to FINANCE.EMPLOYEE
-- The following are valid schema statements: one with a schema
-- context, the other without.
SELECT ID FROM FINANCE.EMPLOYEE;
SELECT EMP_ID FROM SMITH.EMPLOYEE
-- The following statement will resolve to schema SMITH without
-- a schema context
SELECT EMP_ID FROM EMPLOYEE;
See also
solidDB® SQL statements