Managing Database Schemas

A NuoDB domain can contain one or more NuoDB databases. Database users and roles are defined for that specific database and are not shared across databases within the domain. Any NuoDB SQL connection can access only the objects and data in the database specified by the connection request.

Each NuoDB database can contain one or more schemas. Users and roles can be shared across all schemas. However, metadata and data are specific to each schema. Schemas are where database objects are defined, such as tables, views, stored procedures, etc. Objects with the same name can exist in more than one schema without conflict. For example, schema1 and schema2 can both have a table called table1. A database user can access both of these tables providing that user has privileges to do so (see About Database Access and Security). The benefit to having multiple schemas are:

Creating Schemas

Use the CREATE SCHEMA command to create a schema (see CREATE SCHEMA). Schemas can also be created by specifying the schema name using the USE command (see USE. When connected to a schema, objects can be accessed by using just the object name. To access objects in another schema within the same database, use a fully qualified name, consisting the of the schema and object name. Once connected to a database, users can switch to different schemas using the USE command.

Unless a schema is specified when connecting to a NuoDB database, the default schema is USER. All users can create and drop objects in this schema.

A schema cannot contain multiple objects with the same name. The exception to this is a temporary table. Both a temporary and a persistent table can be defined with the same name. Any reference to that table without using a fully qualified name will refer to the temporary table.

A schema can be dropped using the DROP SCHEMA command. To drop the schema, including all contained objects use the CASCADE option.  See DROP SCHEMA.

About the SYSTEM Schema

In addition to the default USER schema and other schemas that are created by users, each database contains a SYSTEM schema, which contains the System Tables. See SQL System Tables for a list of these system tables and usage descriptions.

See also: About Schema Access Levels.