5.3. SCHEMA

Introduced in Firebird 6.0, schemas (schemata) allow you to group related database objects like tables, functions, procedures, etc. into a namespace (the schema). Within a schema, object names must be unique. Between schemas, object can have duplicate names.

This grouping has several purposes

The following object types are always contained in a schema (or, schema-bound):

The following object types are not contained in a schema (or, schemaless):

Initially, a Firebird database has two schemas:

SYSTEM

The schema for system objects (the RDB$*, MON$*, and other system-provided tables, views and packages).

The SYSTEM schema is protected and cannot be dropped.

PUBLIC

A schema for user objects.

The PUBLIC schema is just like any other user-created schema, and can be dropped.

PUBLIC has no special meaning other than:

  • it is created by default in a new database

  • when restoring a database from earlier Firebird versions, all user objects are created in PUBLIC

  • it is listed in the default search path — even if it does not exist

ⓘ
Note

When describing the syntax in various chapters and sections of this Language Reference, we sometimes take shortcuts.

For example, character sets have a schema, but that schema is always SYSTEM, so in some syntax production we will use [SYSTEM <period>] charset instead of the formally more correct [cs-schema <period>] charset and declaring what cs-schema is.

5.3.1. CREATE SCHEMA

Creates a schema.

Available inDSQL

Syntax 

CREATE SCHEMA [IF NOT EXISTS] schema-name
  [DEFAULT CHARACTER SET [SYSTEM <period>] default_charset]
  [DEFAULT SQL SECURITY {DEFINER | INVOKER}]

Table 5.5. CREATE SCHEMA Statement Parameters
ParameterDescription

schema-name

Schema name. The maximum length is 63 characters.

default_charset

Specifies the default character set for character data types created inside this schema. If not specified, it inherits the default database character set. The character set can optionally be qualified with a schema; only SYSTEM has character sets.

The CREATE SCHEMA statement creates a new schema.

When IF NOT EXISTS is specified, the statement will not raise an error if the schema already exists.

Initially, only the schema owner receives the USAGE permission. For other users or roles, it must be explicitly granted.

5.3.1.1. Optional Clauses for CREATE SCHEMA

IF NOT EXISTS

If the schema already exists, no changes will be performed.

DEFAULT CHARACTER SET

The default character set for character data types. If not specified, the default character set of the database is inherited. The default will be used for objects created in the schema except where an alternative character set is used explicitly for a field, domain, variable, cast expression, etc.

The character set name can optionally be qualified with the SYSTEM schema1.

SET DEFAULT SQL SECURITY

Specifies the default SQL SECURITY option to apply at runtime for objects in the schema without the SQL Security property set. If not specified, the SQL SECURITY option of the database is inherited. See also SQL Security in chapter Security.

5.3.1.2. Who Can Create a Schema

The CREATE SCHEMA statement can be executed by:

See alsoSection 5.3.2, “ALTER SCHEMA”, Section 5.3.3, “DROP SCHEMA”, Section 5.3.4, “CREATE OR ALTER SCHEMA”, Section 5.3.5, “RECREATE SCHEMA”

5.3.2. ALTER SCHEMA

Alters a schema.

Available inDSQL

Syntax 

ALTER SCHEMA schema-name
  <alter_schema_option>...
 
<alter_schema_option> ::=
    SET DEFAULT CHARACTER SET [SYSTEM <period>] default-charset
  | SET DEFAULT SQL SECURITY {DEFINER | INVOKER}
  | DROP DEFAULT CHARACTER SET
  | DROP DEFAULT SQL SECURITY

Table 5.6. ALTER SCHEMA Statement Parameters
ParameterDescription

schema-name

Schema name

default-charset

The default character set for character data types created inside this schema. The character set can optionally be qualified with a schema; only SYSTEM has character sets.

The ALTER SCHEMA statement changes the configuration of a schema.

5.3.2.1. Optional Clauses of ALTER SCHEMA

An ALTER SCHEMA statement needs one or more of these clauses:

SET DEFAULT CHARACTER SET

Alters the default character set of the schema.

The character set name can optionally be qualified with the SYSTEM schema1.

DROP DEFAULT CHARACTER SET

Drops the default character set of the schema, inheriting the default character set of the database.

SET DEFAULT SQL SECURITY

Alters the default SQL SECURITY option. See also SQL Security in chapter Security.

DROP DEFAULT SQL SECURITY

Drops the default SQL SECURITY option of the schema, inheriting the default SQL SECURITY option of the database.

5.3.2.2. Who Can Alter a Schema

The ALTER SCHEMA statement can be executed by:

  • Administrators

  • The schema owner

  • Users with the ALTER ANY SCHEMA privilege

See alsoSection 5.3.1, “CREATE SCHEMA”, Section 5.3.4, “CREATE OR ALTER SCHEMA”

5.3.3. DROP SCHEMA

Drops a schema.

Available inDSQL

Syntax 

DROP SCHEMA [IF EXISTS] schema-name

Table 5.7. DROP SCHEMA Statement Parameters
ParameterDescription

schema-name

Schema name

Drops the schema.

When IF EXISTS is specified, the statement will not raise an error if the schema does not exist.

Currently, only empty schemas can be dropped. In the future, we expect a CASCADE sub-clause to be introduced, allowing schemas to be dropped along with all their contained objects.

5.3.3.1. Optional Clauses for DROP SCHEMA

IF EXISTS

If the schema does not exist, no changes will be performed. If this clause is not specified, an error is raised if the schema does not exist.

5.3.3.2. Who Can Alter a Schema

The DROP SCHEMA statement can be executed by:

  • Administrators

  • The schema owner

  • Users with the DROP ANY SCHEMA privilege

See alsoSection 5.3.1, “CREATE SCHEMA”, Section 5.3.5, “RECREATE SCHEMA”

5.3.4. CREATE OR ALTER SCHEMA

Creates or alters a schema.

Available inDSQL

Syntax 

CREATE OR ALTER SCHEMA schema-name
  [DEFAULT CHARACTER SET [SYSTEM <period>] default-charset]
  [DEFAULT SQL SECURITY {DEFINER | INVOKER}]

If the schema does not exist, creates it as if executing the equivalent Section 5.3.1, “CREATE SCHEMA”. Otherwise, alters the schema as if executing Section 5.3.2, “ALTER SCHEMA” with equivalent SET options.

For further details, see Section 5.3.1, “CREATE SCHEMA”.

See alsoSection 5.3.2, “ALTER SCHEMA”, Section 5.3.1, “CREATE SCHEMA”, Section 5.3.5, “RECREATE SCHEMA”

5.3.5. RECREATE SCHEMA

Drops and creates a schema.

Available inDSQL

Syntax 

RECREATE SCHEMA schema-name
  [DEFAULT CHARACTER SET [SYSTEM <period>] default-charset]
  [DEFAULT SQL SECURITY {DEFINER | INVOKER}]

If the schema already exists, drops it as if executing Section 5.3.3, “DROP SCHEMA”. Next, it will create the schema as if executing Section 5.3.1, “CREATE SCHEMA”.

For further details, see Section 5.3.1, “CREATE SCHEMA” and Section 5.3.3, “DROP SCHEMA”.

See alsoSection 5.3.1, “CREATE SCHEMA”, Section 5.3.3, “DROP SCHEMA”