Uniquely identifying objects within catalogs and schemas
Schemas make it possible for two different users to create tables with the same names in the same physical database or even in the same logical database. For example, assume in a single physical database, there are two separate catalogs, employee_catalog and inventory_catalog. Assume also that each catalog contains two separate schemas, named smith and jones, and that the same Smith owns both “smith” schemas and the same Jones owns both “jones” schemas. If Smith and Jones create a table named books in each of their schemas, then we have a total of 4 tables named “books”, and these tables are accessible as:
As you can see, the catalog name and schema name can be used to “qualify” (uniquely identify) the name of a database object such as a table. Object names can be qualified in all DML statements by using the syntax:
catalog_name.schema_name.database_object
or
catalog_name.user_id.database_object
For example:
SELECT cust_name FROM accounting_dept.smith.overdue_bills;
You can qualify one or more database objects with a schema name, whether or not you specify a catalog name. The syntax is:
schema_name.database_object_name
or
user_id.database_object_name
For example,
SELECT SUM(sales_tax) FROM jones.invoices;
To use a schema name with a database object, you must have already created the schema.
By default, database objects that are created without schema names are qualified using the user ID of the database object’s creator. For example:
user_id.table_name
Catalog and schema contexts are set using the SET CATALOG or SET SCHEMA statement.
If a catalog context is not set using SET CATALOG, then all database object names are resolved by using the default catalog name.
Note When creating a new database or converting an old database to a new format, the user is prompted to specify a default catalog name for the database system catalog. Users can access the default catalog name without knowing this specified default catalog name. For example, users can specify the following syntax to access the system catalog:
"".SYSTEM.table
solidDB® translates the empty string ("") specified as a catalog name to the default catalog name. solidDB® also provides for automatic resolution of _SYSTEM schema to the system catalog, even when users provide no catalog name.
The following SQL statements provide examples of creating catalogs and schemas. See solidDB® SQL statements.