Schemas are a collection of database objects, such as tables, views, indexes, events, triggers, sequences, and stored procedures. The use of schemas in solidDB conforms to the SQL standard.
Schemas allow you to provide each user with their own database objects (such as tables) within the same logical database (that is, a single catalog). If no schema is specified with a database object, the default schema is the user id of the user that creates the object.
The schema name is used to qualify a database object name. Database object names are qualified in all DML statements as:
catalog‑name.schema‑name.database‑object‑name
or
user‑id.database‑object‑name
To use schemas, a schema name must be created before creating the database object name (such as a table name or procedure name). However, a database object name can be created without a schema name. In such cases, database objects are qualified by using user‑id only.
You can specify the database object names in a DML statement explicitly by fully qualifying them or implicitly by setting the schema name context by using the SET SCHEMA statement.
A schema can be dropped from a database by using the DROP SCHEMA statement. Before you can drop a schema name, you must first drop all objects that are associated with the schema name.
Rules for resolving schema names:
▪ A fully qualified name (schema‑name.database‑object‑name) does not need any name resolution, but will be validated.
▪ If a schema context is not set using SET SCHEMA, all database object names are resolved always using the user id as the schema name.
▪ If the database object name cannot be resolved from the schema name, then the database object name is resolved from all existing schema names.
▪ If name resolution finds either zero matching or more than one matching database object name, then a solidDB server generates a name resolution conflict error.
The following table describes the statements used to manage schemas.
Note If the autocommit mode is set to OFF, you must commit your work by using the following SQL statement:
COMMIT WORK;
If the autocommit mode is set to ON, the transactions are committed automatically.
Task
Example
Reference
Create a schema
The following example creates a schema named FINANCE and assumes the user id is SMITH:
CREATE SCHEMA FINANCE; CREATE TABLE EMPLOYEE (EMP_ID INTEGER); -- NOTE: The employee table is qualified to SMITH.EMPLOYEE, not -- FINANCE.EMPLOYEE. Creating a schema does not implicitly make -- that new schema the current/default schema. SET SCHEMA FINANCE; CREATE TABLE EMPLOYEE (ID INTEGER); SELECT ID FROM EMPLOYEE; -- In this case, the table is qualified to FINANCE.EMPLOYEE