Manage Database Schemas

A NuoDB domain can contain one or more NuoDB databases.

  • Each database contains schemas.

  • Database users and roles are defined for a 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 contains one or more schemas:

  • A schema contains tables and other database objects. All data exists within a schema.

  • A new database is initialized with the SYSTEM schema.

  • 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, and stored procedures.

  • Objects with the same name can exist in more than one schema without conflict.

    • For example, schema1 and schema2 can both contain a table called table1.

  • A database user can access both of these tables providing that user has privileges to do so (see Database Access and Security).

The benefit to having multiple schemas are:

  • Several users can share one database without interfering with each other.

  • Database objects can be organized into logical groups to make them more manageable.

  • Each application can have its own schema and therefore the database objects in its schema, if named the same as database objects in other schemas, will not collide.

Every new database database is initialized with the SYSTEM schema.

  • Holds database metadata that is read-only.

  • Some metadata is made available via pseudo system tables.

  • See System Tables for a list of system tables and usage descriptions.

Accessing Schemas

Once connected to a database, users can switch between different schemas using the USE command.

  • 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.

  • Unless a schema is specified when connecting to a NuoDB database, the default schema is USER.

Creating Schemas

A user must first be given permission to create new schemas using GRANT. For example to allow user1 to create new schemas:

  • GRANT CREATE on System.SCHEMAS TO user1;

The creator of a schema has full control (to create, modify or delete objects in that schema).

  • Other users must be granted permission to access and/or modify objects in a schema (see GRANT).

  • See also Schema Access Levels.

Three options:

  • Use the CREATE SCHEMA command to explicitly create a schema (see CREATE SCHEMA).

  • If a new object is created with a schema prefix and the schema does not exist, then the schema is created automatically.

  • Run USE <schema> specifying a schema that does not yet exist. If a new object is created then the schema is created automatically.

  • Each of the following create schema1 if it does not already exist:

    SQL> CREATE SCHEMA schema1;

SQL> CREATE TABLE schema1.t1(Name STRING);

SQL> USE schema1
SQL> CREATE TABLE t1(Name STRING);

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 deleted using the DROP SCHEMA command.

  • By default DROP SCHEMA will fail if the schema is not empty.

  • To drop the schema, including all contained objects use the CASCADE option. See DROP SCHEMA.