Schemas are a collection of database objects, such as tables, views, indexes, events, triggers, sequences, and stored procedures for a database user. The default schema name is the user id; there is one default schema for each user. The use of schemas in solidDB® conforms to the SQL standard.
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 logically partition a database, users can create a catalog before they create a schema. For details on creating a catalog, read CREATE CATALOG. When creating a new database or converting an old database to a new format, you are prompted for a default catalog 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 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 using: SET SCHEMA schema_name
Creating a schema does not automatically make that schema the current default schema. If you have created a new schema and want your subsequent commands to execute within that schema, you must also execute the SET SCHEMA statement. For example:
CREATE SCHEMA MySchema; CREATE TABLE t1; -- not in MySchema SET SCHEMA MySchema; CREATE TABLE t2; -- in MySchema
A schema can be dropped from a database using the DROP SCHEMA statement. When dropping a schema name, all objects associated with the schema name must be dropped prior to dropping the schema.
A schema context can be removed using the SET SCHEMA USER statement.
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 issues a name resolution conflict error.
Examples
-- Assume the userID is SMITH. CREATE SCHEMA FINANCE; CREATE TABLE EMPLOYEE (EMP_ID INTEGER); SET SCHEMA FINANCE; CREATE TABLE EMPLOYEE (ID INTEGER); SELECT ID FROM EMPLOYEE; -- In this case, the table is qualified to FINANCE.EMPLOYEE SELECT EMP_ID FROM EMPLOYEE; -- This will give an error as the context is with FINANCE and -- table is resolved to FINANCE.EMPLOYEE -- The following are valid schema statements: one with a schema -- context, the other without. SELECT ID FROM FINANCE.EMPLOYEE; SELECT EMP_ID FROM SMITH.EMPLOYEE -- The following statement will resolve to schema SMITH without -- a schema context SELECT EMP_ID FROM EMPLOYEE;