Skip to main content

Schema definitions

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

Every SCHEMA belongs to a database. One reason for organizing the logically separated data in schemas rather than databases is that in this way there will still be just one MonetDB process running.

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

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.

schema_name_clause:
   ident | [ ident ] AUTHORIZATION ident

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.

schema_element:
   grant | revoke | create_statement | drop_statement | alter_statement

schema:
  | DROP SCHEMA qname drop_action

drop_action:
   RESTRICT | CASCADE

The drop_action option is supported for compatibility with the SQL standard, however it is not implemented yet. Currently it runs with the CASCADE option, meaning that once a schema is dropped every object inside the schema is dropped as well, such as tables and functions. However objects that are dependent on the schema, such as users will not automatically be dropped and will stop the schema from being dropped as well. One can either ALTER the user and give it a different default schema, or to simply drop the user if it is no longer needed.

  | SET SCHEMA ident

When opening the database, by default the “sys” schema is set. Another automatically created schema is “tmp”, used for temporally local tables. It is not possible for a user to access the “tmp” schema of another user.

One can create a new schema using the CREATE command and change to it, by using the SET command. When creating a table without specifying the schema, the table will be created in the schema that is currently in usage. In order to create a table in a different schema, use the schema name as a prefix.