SQL-92 Objects: Tables, Domains, Constraints and other Schema Thingies

There's nothing wrong with thinking that a database is a collection of tables, and a table is a collection of rows, and a row is a collection of columns. Everybody thinks that way. Only, it's more complex when we try to be exhaustive about the possibilities.

The real SQL hierarchy is like this. At the top is a CLUSTER, which you won't see much about because it isn't the object of any SQL verbs, but in Ocelot's implementation it's pretty well what you'd think of as a "database". At the top level we also have USERs, but no other object is top-level. Within a cluster there can be several CATALOGs. Within a catalog there can be several SCHEMAs (sometimes the plural is SCHEMATA). Within a schema there can be several TABLEs, several COLLATIONs, several CHARACTER SETs, several TRANSLATIONs, several DOMAINs, several CONSTRAINTs, and/or several PRIVILEGEs. Within a table there can be several COLUMNs and several CONSTRAINTs.

CATALOG. There is no CREATE CATALOG statement, but we implicitly make new ones with SET CATALOG. The default catalog is OCELOT.

COLLATION. Made with CREATE COLLATION. See Character Sets and Collations for a fuller description of this object.

COLUMN. Made with CREATE TABLE or ALTER TABLE. A column is associated with a datatype. There is always at least one column in a table; there may be hundreds.

CONSTRAINT. Made with CREATE ASSERTION or with ALTER TABLE or as part of the CREATE TABLE statement. See Constraints for a fuller description of this object.

CHARACTER SET. Made with CREATE CHARACTER SET. See Character Sets and Collations for a fuller description of this object. The default character set is ISO8BIT. A character set is associated with "repertoire" and a "form-of-use", which are technically objects too but we don't include them in this list.

CLUSTER. Usually a conceptual thing, but in Ocelot's implementation the cluster is in fact associated with a file that the DBMS reads when you CONNECT. The default cluster is OCELOT.

DOMAIN. Made with CREATE DOMAIN. Every column has a domain, but it's usually implicit. You can associate constraints with domains, either by specifying them in the original CREATE DOMAIN definition or using ALTER DOMAIN.

PRIVILEGE. Made with GRANT. The possible privileges that you can GRANT are: USAGE, SELECT, INSERT, REFERENCES, EXECUTE, DELETE, and UPDATE. A privilege is always associated with a single user and a single object, one of {table, column, character set, collation, translation, domain, routine}.

SCHEMA. Made with CREATE SCHEMA and changed with SET SCHEMA. The default schema is OCELOT.

TABLE. There are two kinds: base tables (made with CREATE TABLE) and views (made with CREATE VIEW). Tables contain columns.

TRANSLATION. Made with CREATE TRANSLATION. Rarely used.

USER. Actually the SQL-92 term is <AuthorizationID>. There is no CREATE USER statement, but THE OCELOT SQL DBMS implicitly makes a new user U when you GRANT to U, when you CONNECT with authorization = U, or when you CREATE SCHEMA for U.

Return to Ocelot home page