Skip to main content

Schema definitions

A SCHEMA is a logical container for objects such as tables, views, indices, sequences, triggers, functions, aggregates and procedures.  Schema access and modification is strictly controlled using the user role and authorisation scheme.

A database contains multiple schemas. Initially a database already contains several system schemas such as sys, tmp, profiler, etc. which are required by the system. You can view the existing schemas in your database by running query: SELECT * FROM sys.schemas;
One reason for organizing your data in schemas rather than databases is that in this way there will still be just one MonetDB process running serving multiple schemas each with their own tables. You can access the tables (or other object) in different schemas by using the schema name prefix: myschema.myobjectname .

create schema:
   CREATE SCHEMA [ IF NOT EXISTS ] schema_name_clause
      [ DEFAULT CHARACTER SET ident ]
      [ PATH schema_name ','... ]
      [ schema_element ','... ]

schema_name_clause:
   schema_name
   | [ schema_name ] AUTHORIZATION ident

schema_element:
   grant | revoke | create_statement | drop_statement | alter_statement

get current schema:
   SELECT CURRENT_SCHEMA

set schema:
   SET SCHEMA schema_name

alter schema:
   ALTER SCHEMA [ IF EXISTS ] schema_name RENAME TO new_schema_name

drop schema:
   DROP SCHEMA [ IF EXISTS ] schema_name [ drop_action ]

drop_action:
   RESTRICT | CASCADE

One can create a new schema using the CREATE command and change to it, by using the SET command. When creating a table (or other object) without specifying the schema, the table (or other object) will be created in the schema that is currently in usage. You can retrieve the current schema name via query: SELECT CURRENT_SCHEMA; In order to create a table in a different schema, use the schema name as a prefix like: myschema.mytable.

The DEFAULT CHARACTER SET and PATH options are here for compatibility reasons with the the SQL standard, however they are not (yet) implemented. The default character set is UTF-8.

The AUTHORIZATION option allows specifying the name of the user or the role that will own the schema. If omitted, the user who has executed the query will be the owner. The owner of the schema is allowed to create, alter and drop tables. With the AUTHORIZATION option, an explicit name for the schema is optional. If omitted, the schema automatically gets the name of the authorised user/role as its name.

Notes on schema creation:

  1. The ownership of a schema can be assigned to only one user/role, and it can not be modified after its creation. Therefore, to share the ownership of a schema, one must assign the ownership of a schema to a role at the creation of the schema. Subsequently, the role can be granted to multiple users to own the schema. 
  2. Only the 'monetdb' user and the 'sysadmin' role can create a new schema. Therefore, to allow other users to create schemas, the 'monetdb' user should assign the 'sysadmin' role to the intended users.