Background documentationSchemas Locate this document in the navigation structure

 

You can use schemas to group database objects that logically belong together. A database object can belong to precisely one schema. Schemas are created implicitly or explicitly:

  • When you create a new database, the database system creates the database system administrator and implicitly creates a schema with the name of the database system administrator and other schemas for the database system administrator. In the demo database DEMODB, for example, were created the schemas DBADMIN, DOMAIN and SYSINFO.

  • When you create a new database user, the database system implicitly creates a schema with the name of the database user. In the demo database DEMODB, for example, was created the schema MONA.

  • Database administrators can create schemas explicitly. In the demo database DEMODB, for example, users DBADMIN or MONA can create schemas.

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

Creating a Schema

Open a database session for the database administrator MONA and use the CREATE SCHEMA statement to create schemas.

CREATE SCHEMA schema_1

//

CREATE SCHEMA schema_2

The schemas SCHEMA_1 and SCHEMA_2 are created.

See also: SQL Reference Manual, CREATE SCHEMA Statement (create_schema_statement)

Setting Schema for a Database Session

Open a database session for the database administrator MONA and use the SET statement to specify a schema different from schema MONA as the current schema for the database session. This means that if a schema name is not specified in subsequent SQL statements, this schema is assumed implicitly.

SET CURRENT_SCHEMA = schema_1

The schema SCHEMA_1 is specified as the current schema for the database session.

See also: SQL Reference Manual, SET Statement (set_statement)

Creating Database Objects in a Schema

If the database user does not enter a schema when creating the database object, the database system assigns the database object automatically to the current schema. Open a database session for the database administrator MONA and use the SET statement to specify the SCHEMA_1 as the current schema. The database user MONA creates the table PERSON_1. Table PERSON_1 is then created in the SCHEMA_1.

CREATE TABLE person_1 (pno_1 FIXED(6), name_1 CHAR(20), city_1 CHAR(20))

If you didn't use the SET Statement to specify SCHEMA_1 as current schema, the table PERSON_1 is created in the schema MONA, because the schema of the user logged on (MONA) is the current schema.

The database user who creates the database object can explicitly assign the database object to a schema. In the next example the database user MONA creates the table PERSON_2 and assigns it to the schema SCHEMA_2.

CREATE TABLE schema_2.person_2 (pno_2 FIXED(6), name_2 CHAR(20), city_2 CHAR(20))

Use in SQL statements the specification of the schema if the object belongs to another schema then the current schema. After inserting data into tables you can for example access table data using the following SELECT statement:

SELECT * FROM schema_2.person_2

For more information about accessing system table data, see Definitions of the System Tables.

Renaming Schemas

Open a database session for the database administrator MONA (the current schema is MONA) and use the RENAME SCHEMA statement to rename a schema.

RENAME SCHEMA schema_2 schema_3

Schema SCHEMA_2 is renamed to SCHEMA_3.

See also: SQL Reference Manual, RENAME SCHEMA Statement (rename_schema_statement)

Deleting Schemas

Open a database session for the database administrator MONA (the current schema is MONA) and use the DROP SCHEMA statement to delete schemas.

DROP SCHEMA schema_1

//

DROP SCHEMA schema_3

Schemas SCHEMA_1 and SCHEMA_3 are deleted.

See also: SQL Reference Manual, DROP SCHEMA Statement (drop_schema_statement)

More Information

More examples for Authorization, Data Definition