Chapter 18 – SQL Table and View

Note

You are reading a digital copy of SQL-99 Complete, Really, a book that documents the SQL-99 standard.

The book and the standard does not reflect the features of CrateDB, we are just publishing it as a service for the community and for reference purposes.

On the one hand, CrateDB does not implement the SQL-99 standard thoroughly, on the other hand, it extends the standard for implementing cluster features and others.

For more information specific to CrateDB, check out the CrateDB Reference documentation.

In this chapter, we’ll describe SQL Tables in detail, and show you the syntax to use to create, alter and destroy them.

A Schema may contain zero or more Tables. An SQL Table is a collection of rows: one header-row of <Column name>s and zero or more rows of data values. (We’ll use the word “row” to mean a row of data values from now on; note that a row of a Table is an instance of an SQL <row type>.) Tables are dependent on some Schema – the <Table name> must be unique within the Schema the Table belongs to – and are created, altered and dropped using standard SQL statements. The Objects that may belong to a Table are Columns and Table Constraints; they depend on some Table. The number of Columns in a Table is the degree of the Table. The number of rows in a Table is the cardinality of the Table. An empty Table has a cardinality of zero.

Tables contain data (as atomic values at the intersection of each row and <Column name>) about a specific entity. Each row of the Table describes a single occurrence of that entity. Rows are the smallest unit of Table insertion and deletion. Table rows are unordered, but an order can be imposed on them during retrieval. Table Columns are ordered, from left to right – their ordinal position matches the ordinal position of the <Column definition> in the Table’s definition.

SQL supports two types of Tables: the physically existent, named Base table and the derived, named View. We use the word “Table” to mean both Base tables and Views throughout this book. (SQL also supports the concept of unnamed derived tables – these are tables which are derived from a <query expression>, so we won’t discuss them here.)

Table of Contents

Base Table

A Base table is either a Schema Object (that is, its definition is part of a Schema definition) or a Module Object (that is, its definition is part of a Module definition). Schema Base tables are known as created Base tables and may be either persistent or temporary. Module Base tables are known as declared temporary Tables and may only be temporary.

There are two kinds of Schema Base tables: the persistent Base table and the temporary Base table.

A persistent Base table is autonomous: it exists in its own right – that is, physically stored records that directly represent the Table actually exist on some data storage device. A persistent Base table contains rows of persistent SQL-data and can be accessed from multiple SQL-sessions. You create persistent Base tables with the CREATE TABLE statement.

A temporary Base table is an SQL-session-dependent Table: it can’t be accessed from any other SQL-session. There are two types of temporary Base tables: the global temporary Base table, which can be accessed from any SQL-client Module in a single SQL-session, and the created local temporary Base table, which can be accessed only from the SQL-client Module that refers to it. Temporary Base tables are always empty when an SQL-session begins: their rows are all deleted either at the end of a transaction or at the end of the SQL-session (depending on the Table’s definition). You create global temporary Base tables with CREATE GLOBAL TEMPORARY TABLE and created local temporary Base tables with CREATE LOCAL TEMPORARY TABLE.

There is one kind of Module Base table: the declared local temporary Base table.

• The declared local temporary Base table is a named Table defined (with the DECLARE TABLE statement) in an SQL-client Module. It is effectively materialized the first time it is referenced in an SQL-session and it persists until that SQL-session ends. A declared local temporary Base table can only be accessed by <externally-invoked procedure>s within the SQL-client Module that contains the Table’s declaration. Temporary Base tables are always empty when an SQL-session begins and are always emptied when the SQL-session ends.

Subtables and Supertables

In SQL3, a Table can be defined as a direct subtable of one or more supertables, using the optional CREATE TABLE ... UNDER clause. A subtable inherits every Column from its supertables, but may also define Columns of its own. A maximal supertable is a supertable that is not also a subtable: it, together with all its subtables, makes up a subtable family – thus, a subtable family has exactly one maximal supertable. Any row of a subtable must correspond to exactly one row of each direct supertable, while any row of a supertable corresponds to at most one row of a direct subtable.

Any Table which has a subtable or a supertable also has a row identifier (this is implicitly defined). The row identifier type for a Table with supertables is a subtype of the row identifier type defined for each supertable. A value of a row identifier type can be substituted for a value of another row identifier type if (a) both types are the same or (b) the first is a subtype of the second. The rules for the INSERT, DELETE and UPDATE statements ensure that the rows in the Tables of a subtable family are consistent with one another. Specifically:

If you INSERT a row into a subtable, your DBMS will INSERT a corresponding row (with the same row identifier, and the same values as the values you provided for the subtable’s inherited Columns) into each of that subtable’s supertables, cascading upward in the Table hierarchy. Thus, if you INSERT a row into a maximal supertable, it is the only row inserted.

If you UPDATE a row of a supertable, all inherited Columns in any corresponding rows of that supertable’s subtables are also updated. If you UPDATE a row of a subtable, your DBMS will UPDATE every corresponding row so that their Columns also contain the new values.

If you DELETE a row from a Table, your DBMS will also DELETE every corresponding row in the subtable family.

For example, consider these SQL statements:

CREATE TABLE people (
   given_name VARCHAR(20),
   surname VARCHAR(30),
   sex CHAR(1),
   id_number CHAR(11),
   age SMALLINT,
   address VARCHAR(50));

CREATE TABLE author UNDER people (
   royalty DECIMAL(4,3));

CREATE TABLE customer UNDER people (
   title VARCHAR(20),
   number SMALLINT,
   total DECIMAL(8,2));

A row in a subtable is “contained” in its supertables. This means that, for example, a row could exist for a person in the PEOPLE Table without there being a corresponding row in the AUTHOR Table (if the person described isn’t also an author). It also means that you could INSERT a row for a new author (one that doesn’t correspond to any existing person) into the AUTHOR Table, and your DBMS would automatically create a corresponding row in the PEOPLE Table for that author.

A Base table is defined by a descriptor that contains 13 pieces of information:

1. The <Table name>, qualified by the <Schema name> of the Schema it belongs to.

2. The Base table’s type: either persistent Base table, global temporary Base table or created local temporary Base table (for Schema Base tables) or (only for Module Base tables) declared local temporary Base table.

3. The degree of the Table: the number of Columns that belong to the Table.

4. A descriptor for every Column that belongs to the Table.

5. A descriptor for every Constraint that belongs to the Table.

6. [Obscure Rule] The name of the structured type (if any) associated with the Table.

7. [Obscure Rule] Whether the Table’s rows have the REF VALUE characteristic.

8. [Obscure Rule] A list (possibly empty) of the names of the Table’s direct supertables.

9. [Obscure Rule] A list (possibly empty) of the names of the Table’s direct subtables.

10. [Obscure Rule] The Table’s row type.

11. [Obscure Rule] A non-empty set of the Table’s functional dependencies.

12. [Obscure Rule] A non-empty set of the Table’s candidate keys.

13. [Obscure Rule] An identification of the Table’s preferred candidate key (this may or may not be defined as the Table’s PRIMARY KEY).

The data contained in a Base table is always updatable via the SQL statements INSERT, UPDATE and DELETE. To create a Base table, use the CREATE TABLE statement (either as a stand-alone SQL statement or within a CREATE SCHEMA statement). CREATE TABLE specifies the enclosing Schema, names the Table and defines the Table’s Columns and Constraints. To change an existing Base table, use the ALTER TABLE statement. To destroy a Base table, use the DROP TABLE statement. To declare a temporary Table for a Module, use the DECLARE TABLE statement.

View

A View is a named, derived (or “virtual”) Table: it doesn’t physically exist, although its definition is persistent. Instead, a View is logically derived from one or more existing Tables, and can be thought of as another way of looking at the presented data. Views are either updatable or read-only, depending on their definitions. A View is defined by a descriptor that contains ten pieces of information:

1. The <Table name>, qualified by the <Schema name> of the Schema the View belongs to.

2. The degree of the View: the number of Columns that are part of the View.

3. A descriptor for every Column that is part of the View.

4. The <query expression> that defines how the View is derived.

5. Whether the View is updatable or not.

6. Whether the View definition includes a CHECK OPTION clause and, if so, whether the clause CHECK OPTION CASCADED or CHECK OPTION LOCAL.

7. [Obscure Rule] The name of the structured type (if any) associated with the View.

8. [Obscure Rule] Whether the View’s rows have the REF VALUE characteristic.

9. [Obscure Rule] A list (possibly empty) of the names of the View’s direct supertables.

10. [Obscure Rule] A list (possibly empty) of the names of the Views’s direct subtables.

Depending on the View’s definition, the data shown through a View may be updatable via the SQL statements INSERT, UPDATE and DELETE. To create a View, use the CREATE VIEW statement (either as a stand-alone SQL statement or within a CREATE SCHEMA statement). CREATE VIEW specifies the enclosing Schema, names the View and its Columns and defines the <query expression> that determines how the View is derived and updated. To change an existing View, destroy and then redefine it. To destroy a View, use the DROP VIEW statement.

Table names

A <Table name> identifies a Base table or a View. During a transaction, you can represent a Table with a <Correlation name> – usually to prevent ambiguity and to make your SQL statements easier to read.

<Table name>

A <Table name> identifies a Base table or a View. The required syntax for a <Table name> is either:

<Table name> ::=
[ <Schema name>. ] unqualified name

or, for a declared LOCAL TEMPORARY Table only:

<Table name> ::=
[ MODULE. ] unqualified name

A <Table name> is a <regular identifier> or a <delimited identifier> that is unique (for all Base tables and Views) within the Schema it belongs to. The <Schema name> that qualifies a <Table name> names the Schema that the Table belongs to and can either be explicitly stated, or a default will be supplied by your DBMS, as follows:

• If a <Table name> in a CREATE SCHEMA statement isn’t qualified, the default qualifier is the name of the Schema you’re creating.

• If the unqualified <Table name> is found in any other SQL statement in a Module, the default qualifier is the name of the Schema identified in the SCHEMA clause or AUTHORIZATION clause of the MODULE statement which defines that Module.

Here are some examples of <Table name>s:

TABLE_1
-- a <Table name>

SCHEMA_1.TABLE_1
-- a simple qualified <Table name>

CATALOG_1.SCHEMA_1.TABLE_1
-- a fully qualified <Table name>

MODULE.TABLE_1
-- a declared LOCAL TEMPORARY <Table name>

<Correlation name>

A <Correlation name> (or alias) identifies a variable that ranges over some Table; that is, a variable whose only permitted values are the rows of a given Table. A <Correlation name> is evaluated as an alternate <Table name> during a transaction and is normally used to prevent ambiguity in complex SQL statements. Once you’ve defined a <Correlation name> for a Table, you must use it to refer to that Table throughout the entire transaction – for example, whenever you would normally use the <Table name> to qualify a <Column name>, use the <Correlation name> instead. Since a <Correlation name> is associated with a Table only in the context in which you define it, you can use the same <Correlation name>, for any Table, in other transactions. The required syntax for a <Correlation name> is:

<Correlation name> ::=
unqualified name

A <Correlation name> is a <regular identifier> or a <delimited identifier> that is unique within the Table it represents for the period of a transaction. The scope of a <Correlation name> is either a SELECT statement (or some other query form), a Trigger definition or both (correlation scopes may be nested). Here is an example of a <Correlation name>:

CORRELATION_1
-- a <Correlation name>

The syntax required to define a <Correlation name> for a Table is:

<Table reference> [ AS ] <Correlation name>
   [ (derived <Column name> [ {,derived <Column name> }... ]) ]

A <Table reference> is a reference to some Table – this is usually a named Table (that is, a Base table or a View), but could also be an unnamed Table (for example, the result of a join, a subquery or a query expression). Most often, a <Table reference> is just a <Table name> and we’ll use only that form for now. Here are two equivalent examples (the <keyword> AS is noise and can be omitted):

SELECT First.column_1, Second.column_1
   FROM Table_1 AS First, Table_1 AS Second
   WHERE First.column_2 = Second.column_2;

SELECT First.column_1, Second.column_1
   FROM Table_1 First, Table_1 Second
   WHERE First.column_2 = Second.column_2;

These SQL statements execute a join of TABLE_1 with itself, over matching COLUMN_2 values. What the SELECTs are doing is looking at all possible pairs of TABLE_1 rows and retrieving the COLUMN_1 values from each row where the COLUMN_2 values are equal to one another. To do so, the DBMS must be able to refer to two rows of TABLE_1 at once – and this requires that it be able to distinguish between the two references. The <Correlation name> allows the DBMS to do this: it calls the one row FIRST, and the other row SECOND, just like the <Correlation name>s specified in the SQL statements. To further clarify the request, each <Column name> specified is qualified with the appropriate <Correlation name>, so that the DBMS knows which Column belongs to which of the rows it’s looking at.

Since a <Table reference> can also refer to an unnamed Table that results from a query, it’s sometimes necessary (or at least useful) to be able to name the Columns of the <Table reference> result. The optional derived <Column name> list with the AS <Correlation name> clause allows you to do this. Here’s an example:

SELECT joined_col_1, joined_col_2
FROM   (Table_1 NATURAL JOIN Table_2) AS Joined_Table
       (joined_col_1, joined_col_2, joined_col_3, joined_col_4)
...

In this example, Table_1 NATURAL JOIN Table_2 is a <Table reference> that evaluates to the Table resulting from the NATURAL JOIN operation – and JOINED_TABLE is the <Correlation name> for that result. The Columns of JOINED_TABLE are explicitly given the names JOINED_COL_1, JOINED_COL_2, JOINED_COL_3 and JOINED_COL_4, and these are the names that are used to refer to those Columns throughout the SQL statement. If you use this option, you must specify a unique, unqualified name for every Column of the result Table, even if you’re never going to refer to some of the Columns again.

Column

A Table may contain one or more Columns. An SQL Column is a collection of similar data values that describe the same attribute of the entity that is fully described by the Table that owns the Column. Columns are dependent on some Table – the <Column name> must be unique within the Table the Column belongs to – and are created, altered and dropped using standard SQL statements. The Objects that may belong to a Column are Column Constraints; they depend on some Column.

Columns are ordered within the Table they belong to, from left to right – their ordinal position matches the ordinal position of the <Column definition> in the Table’s definition. All Columns have a nullability characteristic: it determines (a) whether an attempt to INSERT the null value into the Column will fail and (b) whether a SELECT from the Column can ever return the null value. A Column’s nullability characteristic is “possibly nullable” unless one of these situations apply:

• A Column’s nullability characteristic is “known not nullable” if a non-deferrable Constraint/Assertion on the Column evaluates to Column IS NOT NULL or if the Column is based on a Domain and a non-deferrable Constraint/Assertion on that Domain evaluates to VALUE IS NOT NULL.

• A Column’s nullability characteristic is “known not nullable” if a non-deferrable Constraint on the Column is a PRIMARY KEY Constraint.

A Column is defined by a descriptor that contains nine pieces of information:

1. The <Column name>, qualified by the <Table name> of the Table it belongs to. (It is possible to have a Column with a default name, rather than a name you defined for it yourself. In this case, the Column’s descriptor also indicates that the name is an implementation-dependent, or default, name.)

2. The ordinal position of the Column in its Table. (A Table may contain only one referenceable Column: it must be the first Column in the Table.)

3. The Column’s <data type> specification, including its name, length, precision and scale, as applicable (or, if the Column is based on a Domain, the <Domain name>).

4. The name of the Character set that the Column’s set of values must belong to (for character string types).

5. The name of the Column’s default Collation. (This is the Collation that may be used to compare a character string Column’s values in the absence of an explicit COLLATE clause.)

6. Whether reference values must be checked and whether <reference scope check action> specifies RESTRICT or SET NULL (for REF types).

7. Whether the Column is a system-generated Column or not (that is, whether the Column’s <data type> is REF with VALUES ARE SYSTEM GENERATED). If it is, the Table that the Column belongs to is a referenceable Base Table.

8. The Column’s default value (if any).

9. The Column’s nullability characteristic: either “known not nullable” or “possibly nullable”.

To create a Column, use a <Column definition> in a CREATE TABLE or ALTER TABLE statement. To change or destroy an existing Column, use the ALTER TABLE statement.

Column names

A <Column name> identifies a Column. The SQL Standard does not consider a <Column name> to be a qualified name but, in practice, <Column name>s are often qualified to prevent ambiguity in complex SQL statements: this practice is called a <Column reference>. A qualified <Column name> is not allowed in a <Column definition> – that is, a <Column reference> may not be used to identify a Column in a CREATE TABLE or an ALTER TABLE statement.

<Column name>

The required syntax for a <Column name> is:

<Column name> ::=
unqualified name

A <Column name> is a <regular identifier> or a <delimited identifier> that is unique (for all Columns) within the Table it belongs to. Here is an example of a <Column name>:

COLUMN_1
-- a <Column name>

<Column reference>

The required syntax for a <Column reference>, valid only outside of a <Column definition> is either:

<Column reference> ::=
[ Table specification. ] <Column name>

   Table specification ::=
   <Table name> |
   <Correlation name>

or, for a declared LOCAL TEMPORARY Table only:

<Column reference> ::=
MODULE.<Table name>.<Column name>

A <Column reference> is a <Column name> qualified by a Table specification, or by MODULE.<Table name>. The Table specification which qualifies a <Column name> identifies the Table that the Column belongs to, and is either that Table’s name or a <Correlation name> that identifies that Table. If you omit the Table specification from a <Column reference>, it will default to the <Table name> of the Table that owns the Column. Here are some examples of <Column reference>s:

 TABLE_1.COLUMN_1
-- a <Column reference> with a <Table name>

 CORRELATION_1.COLUMN_1
 -- a <Column reference> with a <Correlation name>

 MODULE.TABLE_1.COLUMN_1
 -- a <Column reference> for a declared LOCAL TEMPORARY Table

CREATE TABLE Statement

The CREATE TABLE statement names a new Base table and defines the Table’s Columns and Constraints. The required syntax for the CREATE TABLE statement is:

CREATE [ {GLOBAL TEMPORARY | LOCAL TEMPORARY} ] TABLE <Table name>
   <table contents source>
   [ ON COMMIT {PRESERVE ROWS | DELETE ROWS} ]

   <table contents source> ::=
   (<table element list>) |
   OF <UDT name> [ UNDER <supertable name> [ {,<supertable name>}... ] ] [ <table element list> ]

      <table element list> ::=
      <table element> [ {,<table element>}... ]

         <table element> ::=
         <Column definition> |
         <Table Constraint> |
         LIKE <Table name> |
         <Column name> WITH OPTIONS <column option list>

             <Table Constraint> ::=
             [ CONSTRAINT <Constraint name> ]
             Constraint_type
             [ <constraint attributes> ]

             <column option list> ::=
            [ <scope clause> ]
            [ <default clause> ]
            [ <Column Constraint>... ]
            [ COLLATE <Collation name> ]

CREATE TABLE defines a new persistent Base table. CREATE GLOBAL TEMPORARY TABLE defines a new global temporary Base table. CREATE LOCAL TEMPORARY TABLE defines a new created local temporary Base table. A Table is owned by the Schema it belongs to.

• The <Table name> identifies the Table and the Schema that it belongs to. A <Table name> that includes an explicit <Schema name> qualifier belongs to the Schema named. A <Table name> that does not include an explicit <Schema name> qualifier belongs to the SQL-session default Schema. The <Table name> must be unique (for all Base tables and Views) within the Schema that owns it.

If CREATE TABLE is part of a CREATE SCHEMA statement, the <Table name>, if explicitly qualified, must include the <Schema name> of the Schema being created; that is, it isn’t possible to create a Base table belonging to a different Schema from within CREATE SCHEMA. For example, this SQL statement will not return an error because the <Table name> will default to include the qualifying <Schema name>:

CREATE SCHEMA bob
   CREATE TABLE Table_1 (column_1 SMALLINT);
   -- creates a Table called BOB.TABLE_1 in Schema BOB

This SQL statement will not return an error either because the <Table name> explicitly includes a qualifying <Schema name> that matches the name of the Schema being created:

CREATE SCHEMA bob
   CREATE TABLE bob.Table_1 (column_1 SMALLINT);
   -- creates a Table called BOB.TABLE_1 in Schema BOB

But this SQL statement will return an error because the <Table name> explicitly includes a qualifying <Schema name> that is different from the name of the Schema being created:

CREATE SCHEMA bob
   CREATE TABLE sam.Table_1 (column_1 SMALLINT);
   -- tries to create a Table belonging to Schema SAM inside Schema BOB; illegal syntax

[Obscure Rule] In effect, a temporary Table does not exist until it is invoked during an SQL-session. Once invoked, it will only be visible to the SQL-session (or the Module) that invoked it – that is, the temporary Table will not be visible to other users. At the end of the SQL-session, all temporary Tables invoked during the SQL-session are dropped. Because temporary Tables are materialized only when invoked, the Schema they belong to is actually defined by your DBMS.

• If you’re creating a temporary Table with CREATE GLOBAL TEMPORARY TABLE, the <Table name> may not be explicitly qualified. Because a global temporary Table is distinct within an SQL-session, the Schema it belongs to is a Schema determined by your DBMS – in effect, it fixes a qualifying <Schema name> for the Table based on the Schema in which the global temporary Table is defined, coupled with the DBMS’s name for the SQL-session in which you invoke that Table.

• If you’re creating a temporary Table with CREATE LOCAL TEMPORARY TABLE, the <Table name> may not be explicitly qualified. Because a local temporary Table is distinct within an SQL-client Module within an SQL-session, the Schema it belongs to is a Schema determined by your DBMS – in effect, it fixes a qualifying <Schema name> for the Table based on the Schema in which the global temporary Table is defined, coupled with the DBMS’s name for the SQL-session in which you invoke that Table, coupled with the DBMS’s name for the SQL-client Module that refers to that Table.

If CREATE TABLE is executed as a stand-alone SQL statement, the current <AuthorizationID> must either be the owner of the Schema that this new Table belongs to, or the Schema’s owner must be a Role that the current <AuthorizationID> may use. That is, only the owner of a Schema can create Tables for that Schema. In addition to creating a Table, CREATE TABLE also causes the SQL special grantor, “_SYSTEM”, to grant grantable INSERT, SELECT, UPDATE, DELETE, TRIGGER and REFERENCES Privileges on the new Table, as well as grantable SELECT, INSERT, UPDATE and REFERENCES Privileges on every Column in the new Table, to the Schema owner <AuthorizationID> (that is, the <AuthorizationID creating the Table).

<table contents source clause>

The <table contents source> clause defines the structure of the Table’s contents: it tells you what sort of data the Table contains. This clause is either a list of elements, such as Column and Table Constraint definitions, or an OF clause that defines the UDT structure that makes up the Table. Every <table element list> has to contain at least one <Column definition>.

[Obscure Rule] If CREATE TABLE includes the OF clause, then <UDT name> must identify a structured type. If the OF clause also includes the (optional) <table element list>, the list (a) may not contain a LIKE clause and (b) may contain only one <table element> that is a <Column definition>: it must define a Column with a REF <data type> that specifies VALUES ARE SYSTEM GENERATED. The optional UNDER sub-clause in the OF clause contains a list of <Table name>s. Each <Table name> in the list identifies a direct supertable of the Table being created and must therefore belong to the same Schema that owns this new Table. (The Schema owner <AuthorizationID> must have the UNDER Privilege on each supertable named.) A <Table name> may appear in the UNDER list only once. The new Table is a direct subtable of each of its direct supertables: this subtable family must have exactly one maximal supertable. (The effect of CREATE TABLE on the new Table’s supertables is that its name is added to the list of direct subtables in each supertable’s definition.) If you add the UNDER sub-clause to a CREATE TABLE statement: (a) the structured type identified by <UDT name> must be a direct subtype of the structured type of every direct supertable of the new Table, (b) your Table definition may not include a PRIMARY KEY Constraint, (c) one of the supertables of the new Table must include a UNIQUE Constraint that constrains a Column with a nullability characteristic of “known not nullable” and (d) the Schema owner’s <AuthorizationID> is granted grantable SELECT, UPDATE and REFERENCES Privileges for every inherited Column of the new Table. (The grantor of these Privileges is the SQL special grantor, “_SYSTEM”.) Note that if a direct supertable of the new Table is a referenceable Base table, then this new Table is also a referenceable Base table. In this case, your OF clause, if it contains a <table element list>, may not include a <Column definition>. If any <table element> contains a <scope clause> the Base table(s) referred to therein must be either (a) persistent Base tables, if CREATE TABLE contains no <table scope>, (b) GLOBAL TEMPORARY Base tables if you’re creating a GLOBAL TEMPORARY Base table or (c) created LOCAL TEMPORARY Base tables if you’re creating a LOCAL TEMPORARY Base table. For further details, refer to our chapter on User-defined Types.

The common form of CREATE TABLE uses a parenthesized <table element list> as its <table contents source> clause. A <table element> is either a <Column definition> (see “<Column definition>” in this chapter , a <Table Constraint> (see our chapter on Constraints and Assertions), a LIKE clause or a <column options> clause. Multiple <table element>s must be separated by commas.

The effect of CREATE TABLE <Table name> (<Column definition>,<Column definition>), e.g.:

CREATE TABLE Table_1 (
   column_1 SMALLINT,
   column_2 DATE);

is to create a Table, called TABLE_1, that contains two Columns, called COLUMN_1 and COLUMN_2. At least one Column must be defined in a <table element list>. The row type of the new Table is the set of pairs (<Column name>, <data type>) defined for the Table.

The effect of CREATE TABLE <Table name> (<Column definition>,<Table Constraint>), e.g.:

CREATE TABLE Table_1 (
   column_1 SMALLINT,
   CONSTRAINT constraint_1 PRIMARY KEY(column_1));

is to create a Table, called TABLE_1, that contains one Column, called COLUMN_1, and one <Table Constraint> that defines COLUMN_1 as the Table’s primary key. Zero or more <Table Constraint>s may be defined in a <table element list>. If the new Table is a temporary Base table, then all Tables referred to in the new Table’s <Table Constraint>s must also be temporary Base tables.

The effect of CREATE TABLE <Table name> (LIKE <Table name>) is to create a Table whose <Column definitions>s are copied from another Table. In the LIKE clause, <Table name> identifies the Table whose <Column definition>s you want to copy into the new Table. For example, this SQL statement creates a Table in the usual manner:

CREATE TABLE Table_1 (
   column_1 SMALLINT,
   column_2 DATE,
   column_3 CHAR(8),
   CONSTRAINT constraint_1 CHECK (column_1 BETWEEN 50 AND 5000));

And this SQL statement uses the LIKE clause to create another Table with the same <Column definition>s:

CREATE TABLE Table_2 (LIKE Table_1);

The result is a Table, called TABLE_2, whose structure will be exactly as if it had been defined with this SQL statement:

CREATE TABLE Table_2 (
   column_1 SMALLINT,
   column_2 DATE,
   column_3 CHAR(8));

Note that the <Table Constraint> from TABLE_1’s definition is not recopied into TABLE_2’s definition: the LIKE clause copies only <Column definition>s. However, because a <Column Constraint> is effectively replaced by a <Table Constraint> in a Table’s definition, the LIKE clause also won’t copy any <Column Constraint>s from the existing Table into the new Table. For example, for these two SQL statements:

CREATE TABLE Table_1 (
   column_1 SMALLINT NOT NULL,
   column_2 DATE,
   column_3 CHAR(8));

CREATE TABLE Table_2 (LIKE Table_1);

the result in the first case is a Table, called TABLE_1, whose structure will be exactly as if it had been defined with this SQL statement:

CREATE TABLE ``TABLE_1`` (
   column_1 SMALLINT,
   column_2 DATE,
   column_3 CHAR(8)
   CONSTRAINT CHECK (column_1 IS NOT NULL));

and thus, the result in the second case is a Table, called TABLE_2, whose structure will be exactly as if it had been defined with this SQL statement:

CREATE TABLE Table_2 (
   column_1 SMALLINT,
   column_2 DATE,
   column_3 CHAR(8));

If CREATE TABLE includes a LIKE clause, the current <AuthorizationID> must have the SELECT Privilege on the Table named.

The effect of CREATE TABLE <Table name> (<Column definition,<Column name> WITH OPTIONS <column option list>) is to create a Table containing one or more Columns whose definitions are further outlined by the <column option>(s) chosen.

Temporary Tables

If you’re creating a temporary Table, you may also use the ON COMMIT clause to specify whether you want the Table to be emptied whenever a COMMIT statement is executed. If you omit the ON COMMIT clause from CREATE TEMPORARY TABLE, it defaults to ON COMMIT DELETE ROWS. For example, these two SQL statements are equivalent:

CREATE GLOBAL TEMPORARY TABLE Table_1 (
   column_1 SMALLINT);

CREATE GLOBAL TEMPORARY TABLE Table_1 (
   column_1 SMALLINT)
   ON COMMIT DELETE ROWS;

Based on this Table definition, the effect of these two SQL statements:

INSERT INTO Table_1 (column_1)
VALUES(10);

COMMIT;

is that TABLE_1 is first materialized and has data inserted into it, and then the rows are deleted. That is, at COMMIT time, your DBMS effectively executes this SQL statement:

DELETE FROM Table_1;

since the definition of TABLE_1 states that the Table is to be emptied at COMMIT. On the other hand, the effect of these three SQL statements:

CREATE GLOBAL TEMPORARY TABLE Table_1 (
   column_1 SMALLINT)
   ON COMMIT PRESERVE ROWS;

INSERT INTO Table_1 (column_1)
VALUES(10);

COMMIT;

is that TABLE_1 is created, materialized and has data inserted into it, and then the rows are committed. That is, at COMMIT time, your DBMS does not delete the rows, since TABLE_1’s definition explicitly says not to. (The rows will, however, be deleted at the end of the SQL-session.)

If you want to restrict your code to Core SQL, don’t create any temporary Base tables, don’t use a LIKE clause as a <table element>, don’t use an OF clause as a <table element> and don’t add a <column scope clause> to any CREATE TABLE statement.

<Column definition>

A <Column definition> is used to create or alter a Column of a Base table. Used in a CREATE TABLE or an ALTER TABLE statement, it names a Column and defines the Column’s <data type>, default value and Constraints. The required syntax for a <Column definition> is:

<Column definition> ::=
unqualified <Column name>
{ <data type> | <Domain name> }
[ <reference scope check> ]
[ DEFAULT default value ]
[ <Column Constraint> list ]
[ COLLATE <Collation name> ]

   <Column Constraint> list ::=
   <Column Constraint> [ <Column Constraint> ]

      <Column Constraint> ::=
      [ CONSTRAINT <Constraint name> ]
      Constraint_type
      [ <constraint attributes> ]

   <reference scope check> ::=
   REFERENCES ARE [ NOT ] CHECKED
   [ ON DELETE <reference scope check action> ]

      <reference scope check action> ::= RESTRICT | SET NULL

A <Column definition> defines a new Column for a Base table. A Column is owned by the Table it belongs to. The <Column name> identifies the Column and the Table that it belongs to. A <Column name> in a <Column definition> may not be qualified: it belongs to the Table named in the enclosing CREATE TABLE or ALTER TABLE statement. The <Column name> must be unique within the Table that owns it.

<data type>

A Column must be defined to accept a certain type of data. This is done in one of two ways: you can either define a Column with a <data type> specification> or you can define it as being based on a Domain. If you base a Column on a Domain, your current <AuthorizationID> must have the USAGE Privilege on that Domain. The Column’s specified <data type> (or the <data type> of the Domain it is based on) constrains the values that can be accepted by the Column. The <data type> specification includes length, precision and scale as applicable. Valid <data type>s are: INT, SMALLINT, NUMERIC(p,s), DECIMAL(p,s), FLOAT(p), REAL, DOUBLE PRECISION, BIT(l), BIT VARYING(l), BLOB(l), CHAR(l), NCHAR(l), VARCHAR(l), NCHAR VARYING(l), CLOB(l), NCLOB(l), DATE, TIME(p), TIME(p) WITH TIME ZONE, TIMESTAMP(p), TIMESTAMP(p) WITH TIME ZONE, INTERVAL <interval qualifier>, BOOLEAN, ARRAY, ROW and REF.

The effect of the syntax CREATE TABLE <Table name> (<Column name> <data type>) is to define a Column with a <data type> specification. For example, this SQL statement creates a Table with a Column that has a <data type> of DECIMAL(9,2):

CREATE TABLE Table_1 (
   column_1 DEC(9,2));

The effect of the syntax CREATE TABLE <Table name> (<Column name> <Domain name>) is to define a Column based on a Domain. For example, the effect of these SQL statements is also a Table with a Column that has a <data type> of DECIMAL(9,2):

CREATE DOMAIN domain_1 AS DEC(9,2);

CREATE TABLE Table_1 (
   column_1 domain_1;

[Obscure Rule] If the <data type> of a Column is CHAR, VARCHAR or CLOB, the Character set that the Column’s values must belong to is determined as

follows:

• If the <Column definition> contains a <data type> specification that includes a CHARACTER SET clause, the Column’s Character set is the Character set named. Your current <AuthorizationID> must have the USAGE Privilege on that Character set.

• If the <Column definition> does not include a <data type> specification, but the Column is based on a Domain whose definition includes a CHARACTER SET clause, the Column’s Character set is the Character set named.

• If the <Column definition> does not include any CHARACTER SET clause at all – either through a <data type> specification or through a Domain definition – the Column’s Character set is the Character set named in the DEFAULT CHARACTER SET clause of the CREATE SCHEMA statement that defines the Schema that the Column belongs to.

For example, the effect of these two SQL statements:

CREATE SCHEMA bob AUTHORIZATION bob
   DEFAULT CHARACTER SET INFORMATION_SCHEMA.LATIN1;

CREATE TABLE Table_1 (
   column_1 CHAR(10));

is to create a Table, with one Column, in Schema BOB. The Column’s set of valid values are fixed length character strings, exactly 10 characters long, all of whose characters must be found in the INFORMATION_SCHEMA.LATIN1 Character set – the Schema’s default Character set.

The effect of these two SQL statements:

CREATE SCHEMA bob AUTHORIZATION bob
   DEFAULT CHARACTER SET INFORMATION_SCHEMA.LATIN1;

CREATE TABLE Table_1 (
   column_1 CHAR(10) CHARACTER SET INFORMATION_SCHEMA.SQL_CHARACTER);

is to create the same Table with one difference: this time, its Column’s values must consist only of characters found in the INFORMATION_SCHEMA.SQL_CHARACTER Character set – the explicit Character set specification in CREATE TABLE constrains the Column’s set of values. The Schema’s default Character set does not.

[Obscure Rule] If the <data type> of a Column is CHAR, VARCHAR, CLOB, NCHAR, NCHAR VARYING or NCLOB, and your <Column definition> does not include a COLLATE clause, the Column has a coercibility attribute of COERCIBLE – but if your <Column definition> includes a COLLATE clause, the Column has a coercibility attribute of IMPLICIT. In either case, the Column’s default Collation is determined as follows:

• If the <Column definition> includes a COLLATE clause, the Column’s default Collation is the Collation named. Your current <AuthorizationID> must have the USAGE Privilege on that Collation.

• If the <Column definition> does not include a COLLATE clause, but does contain a <data type> specification that includes a COLLATE clause, the Column’s default Collation is the Collation named. Your current <AuthorizationID> must have the USAGE Privilege on that Collation.

• If the <Column definition> does not include a COLLATE clause, but the Column is based on a Domain whose definition includes a COLLATE clause, the Column’s default Collation is the Collation named.

• If the <Column definition> does not include any COLLATE clause at all – either explicitly, through a <data type> specification or through a Domain definition – the Column’s default Collation is the default Collation of the Column’s Character set.

[Obscure Rule] If the <data type> of a Column is REF(UDT), your current <AuthorizationID> must have the USAGE Privilege on that UDT. If the <data type> of a Column includes REF with a <scope clause>, your <Column definition> must also include a <reference scope check> clause, to indicate whether references are to be checked or not (don’t add a <reference scope check> clause under any other circumstances). In this case, you may also add the optional <reference scope check action> clause, to indicate the action to be taken when the Column is the subject of a DELETE statement. If you omit the <reference scope check action> clause, it defaults to ON DELETE RESTRICT.

• If a Column is defined with REFERENCES ARE CHECKED and a <scope clause> naming one or more Tables is included in the <Column definition>, then there is an implied DEFERRABLE INITIALLY IMMEDIATE Constraint on the new Column which checks that the Column’s values are also found in the system generated Column of each Table named in the <scope clause>. In this case, if the <reference scope check action> is SET NULL then, prior to deleting any rows from the Table that owns this Column, your DBMS will (a) execute a SET CONSTRAINT statement that sets the implied Constraint’s constraint check time to DEFERRED, (b) DELETE the rows as required, (c) set the value of the system generated Column in each Table named in the <scope clause> to NULL, for each row that matched the deleted rows and (d) execute a SET CONSTRAINT statement that sets the implied Constraint’s constraint check time to IMMEDIATE.

DEFAULT Clause

The optional DEFAULT clause defines the Column’s default value: the value to insert whenever this Column is the target of an INSERT statement that doesn’t include an explicit value for it. The DEFAULT options are: DEFAULT <literal>, DEFAULT CURRENT_DATE, DEFAULT CURRENT_TIME(p), DEFAULT CURRENT_TIMESTAMP(p), DEFAULT LOCALTIME(p), DEFAULT LOCALTIMESTAMP(p), DEFAULT USER, DEFAULT CURRENT_USER, DEFAULT SESSION_USER, DEFAULT SYSTEM_USER, DEFAULT CURRENT_PATH, DEFAULT ARRAY[], DEFAULT ARRAY??(??) and DEFAULT NULL -- see "<default clause>", later in this chapter. The ``DEFAULT clause is optional whether or not the Column is based on a Domain that has a defined default value, so a Column’s default value is determined as follows:

• If a <Column definition> that contains a <data type> specification omits the DEFAULT clause, the Column has no default value.

• If a <Column definition> that contains a <data type> specification includes the DEFAULT clause, the Column’s default value is the default value specified – that is, the syntax CREATE TABLE <Table name> (<Column name> DEFAULT default value) defines a Column with an explicit default value. For example, this SQL statement creates a Table with a Column whose default value is the <character string literal> 'bobby':

CREATE TABLE Table_1 (
   column_1 CHAR(5) DEFAULT 'bobby');

• If a Column is based on a Domain and the <Column definition> omits the DEFAULT clause, the Column’s default value is the Domain’s default value. If the Domain has no defined default value, then the Column has no default value either. For example, the effect of these two SQL statements is to define a Column whose default value is the <character string literal> 'bobby' – taken from the Domain that the Column is based on:

CREATE DOMAIN domain_1 AS
   CHAR(5) DEFAULT 'bobby';

CREATE TABLE Table_1 (
   column_1 domain_1);

If a Column is based on a Domain and the <Column definition> includes the DEFAULT clause, the Column’s default value is the default value specified – even if the Domain has a defined default value. The <Column definition>’s DEFAULT clause always over-rides any default value defined for the Domain that a Column is based on. For example, the effect of these two SQL statements is to define a Column whose default value is the <character string literal> 'bobby' – despite the fact that the Domain that the Column is based on has a default value that is the <character string literal> 'sammy':

CREATE DOMAIN domain_1 AS
   CHAR(5) DEFAULT 'sammy';

CREATE TABLE Table_1 (
   column_1 domain_1 DEFAULT 'bobby');

<Column Constraint>s

The optional <Column Constraint> list clause is used to define zero or more <Constraint>s on the Column: the Constraint rules will restrict the Column’s set of valid values – see our chapter on Constraints and Assertions. (If the Column is based on a Domain, the Column’s set of valid values is restricted by both the Domain’s Constraints and the Column’s Constraints.) All <Constraint name>s must be unique within the Schema that the Column belongs to. The syntax CREATE TABLE <Table name> (<Column name> <data type> <Column Constraint>, <Column name> <Domain name> <Column Constraint>) defines a Column whose definition includes a <Column Constraint>. Here is an example:

CREATE TABLE Table_1 (
   column_1 SMALLINT NOT NULL,
   column_2 domain_1 PRIMARY KEY NOT DEFERRABLE);
   -- column_1 excludes null values and column_2 is the Table's primary key

The syntax CREATE TABLE <Table name> (<Column name> <data type> DEFAULT default value <Column Constraint>, <Column name> <Domain name> DEFAULT default value <Column Constraint>) also defines Columns whose definitions include a <Column Constraint>. Here is an example:

CREATE TABLE Table _1 (
   column_1 SMALLINT DEFAULT 100
      CONSTRAINT constraint_1 PRIMARY KEY NOT DEFERRABLE
   column_2 domain1 DEFAULT 'bobby'
      CONSTRAINT constraint_2 CHECK (column_2 IS NOT NULL);
   -- column_1 is the Table's primary key and column_2 excludes null values

A <Column Constraint> is valid only in a <Column definition> because, once defined, <Column Constraint>s logically become <Table Constraint>s of the Table that the Column belongs to. To change or drop a <Column Constraint>, or to add a <Column Constraint> to a Table once CREATE TABLE has been executed, use the ALTER TABLE statement.

If you want to restrict your code to Core SQL, don’t add a COLLATE clause to your <Column definition>s, don’t base your Columns on Domains, don’t name your <Column Constraint>s and don’t define a <Column Constraint> with a <referential triggered action>.

<default clause>

A <default clause> defines the default value for a Column, a Domain or an attribute of a UDT. The required syntax for a <default clause> is:

<default clause> ::=
DEFAULT default value

   default value ::=
   <literal> |
   USER |
   CURRENT_USER |
   SESSION_USER |
   SYSTEM_USER |
   CURRENT_PATH |
   CURRENT_DATE |
   CURRENT_TIME[(p)] |
   CURRENT_TIMESTAMP[(p)] |
   LOCALTIME[(p)] |
   LOCALTIMESTAMP[(p)] |
   ARRAY[] |
   ARRAY??(??) |
   NULL

The default value of an Object is the data value that will be inserted into the Object whenever it is the target of an INSERT statement that does not provide an explicit data value for that Object. If the definition of an Object does not include a <default clause>, no default value is assigned to it – so when the Object is the target of an INSERT statement that does not provide an explicit data value for it, your DBMS will INSERT a null value. (If the Object doesn’t allow nulls, the INSERT will, of course, fail.) The <data type> of a default value must match the Object’s <data type> (that is, the default value and the Object’s <data type> must be mutually assignable).

• If the <data type> of an Object is a <reference type>, the <default clause> must be DEFAULT NULL.

• If the <data type> of an Object is a <collection type>, the <default clause> must be DEFAULT NULL or DEFAULT ARRAY[] or DEFAULT ARRAY??(??) or DEFAULT <literal>.

If a <default clause> is DEFAULT <literal>, the value represented by the <literal> is the target Object’s default value. Here are some examples of <default clause>s with a <literal> as the default value:

CREATE DOMAIN domain_1 AS NCHAR(5) DEFAULT N'sammy';

CREATE TABLE Table_1 (column_1 VARCHAR(6) DEFAULT 'bob');

CREATE DOMAIN domain_1 AS BIT VARYING(4) DEFAULT B'0010';

CREATE TABLE Table_1 (column_1 BIT(16) DEFAULT X'4E2C');
   -- If the target Object has a BIT <data type> and the length of your <literal> is less than the defined length of the Object, your DBMS will return the SQLSTATE warning 01008 "warning-implicit zero-bit padding".

CREATE DOMAIN domain_1 AS SMALLINT DEFAULT 100;

CREATE TABLE Table_1 (column_1 REAL DEFAULT 15000);

CREATE DOMAIN domain_1 AS DATE DEFAULT DATE '1994-07-15';

CREATE TABLE Table_1 (
   column_1 INTERVAL MONTH DEFAULT INTERVAL '03' MONTH);

CREATE DOMAIN domain_1 AS BOOLEAN DEFAULT FALSE

If a <default clause> is DEFAULT USER, DEFAULT CURRENT_USER, DEFAULT SESSION_USER or DEFAULT SYSTEM_USER, the value returned by the function is the target Object’s default value. In this case, the target Object must have a character string <data type> with a defined length of at least 128 characters and must belong to the SQL_TEXT Character set. Here are some examples of <default clause>s with a <niladic user function> as the default value:

CREATE DOMAIN domain_1 AS
   CHAR(128) CHARACTER SET SQL_TEXT DEFAULT CURRENT_USER;

CREATE TABLE Table_1 (
   column_1 VARCHAR(256) CHARACTER SET SQL_TEXT DEFAULT SESSION_USER;

If a <default clause> is DEFAULT CURRENT_PATH, the value returned by the function is the target Object’s default value. In this case, the target Object must have a character string <data type> with a defined length of at least 1031 characters and must belong to the SQL_TEXT Character set. Here are some examples of <default clause>s with CURRENT_PATH as the default value:

CREATE DOMAIN domain_1 AS
   CHAR(1031) CHARACTER SET SQL_TEXT DEFAULT CURRENT_PATH;

CREATE TABLE Table_1 (
   column_1 VARCHAR(2000) CHARACTER SET SQL_TEXT DEFAULT CURRENT_PATH;

If a <default clause> is DEFAULT CURRENT_DATE, DEFAULT CURRENT_TIME[(p)], DEFAULT CURRENT_TIMESTAMP[(p)], DEFAULT LOCALTIME[(p)] or DEFAULT LOCALTIMESTAMP[(p)], the value returned by the function is the target Object’s default value. In this case, the target Object must have a datetime <data type> that matches the function’s <data type>. Here are some examples of <default clause>s with a <datetime value function> as the default value:

CREATE DOMAIN domain_1 AS
   DATE DEFAULT CURRENT_DATE;

CREATE TABLE Table_1 (
   column_1 TIME WITH TIME ZONE DEFAULT CURRENT_TIME;

CREATE DOMAIN domain_1 AS
   TIMESTAMP(4) DEFAULT CURRENT_TIMESTAMP(4);

CREATE TABLE Table_1 (
   column_1 TIME(4) DEFAULT LOCALTIME(4);

If a <default clause> is DEFAULT ARRAY[] or DEFAULT ARRAY??(??), an empty array value is the target <collection type>’s default value. Here is an example of a <default clause> with an empty array as the default value:

CREATE DOMAIN domain_1 AS
   INT ARRAY[3] DEFAULT ARRAY[];

If a <default clause> is DEFAULT NULL, the null value is the target Object’s default value. (The Object can’t, of course, have a NOT NULL Constraint.) Here is an example of a <default clause> with a null value as the default value:

CREATE TABLE Table_1 (
   column_1 CHAR(15) DEFAULT NULL;

[Obscure Rule] If a <default clause> that is part of an SQL-Schema statement defines a default value that can’t be represented in INFORMATION_SCHEMA without truncation, your DBMS will return the SQLSTATE warning 0100B "warning-default value too long for information schema.

If you want to restrict your code to Core SQL, don’t use DEFAULT CURRENT_PATH when defining a <default clause>.

ALTER TABLE Statement

The ALTER TABLE statement changes a Base table’s definition. The required syntax for the ALTER TABLE statement is:

ALTER TABLE <Table name> <alter table action>
 <alter table action> ::=
 ADD [ COLUMN ] <Column definition> |
 ALTER [ COLUMN ] <Column name> SET DEFAULT default value |
 ALTER [ COLUMN ] <Column name> DROP DEFAULT |
 ALTER [ COLUMN ] <Column name> ADD SCOPE <Table name list> |
 ALTER [ COLUMN ] <Column name> DROP SCOPE {RESTRICT | CASCADE} |
 DROP [ COLUMN ] <Column name> {RESTRICT | CASCADE} |
 ADD <Table Constraint> |
 DROP CONSTRAINT <Constraint name> {RESTRICT | CASCADE}

  <Table name list> ::=
 (<Table name> [ {,<Table name>}... ]) |
 <Table name>

The <Table name> must identify an existing Base table whose owner is either the current <AuthorizationID> or a Role that the current <AuthorizationID> may use. That is, only the <AuthorizationID> that owns the Table may alter it. ALTER TABLE can be used to change a persistent Base table, a GLOBAL TEMPORARY Base table or a created LOCAL TEMPORARY Base table, but you can’t use it to change a declared LOCAL TEMPORARY Base table.

ADD [ COLUMN ] Clause

The effect of ALTER TABLE <Table name> ADD [COLUMN] <Column definition>, e.g.:

ALTER TABLE Table_1 ADD COLUMN
   column_1 SMALLINT DEFAULT 150
      CONSTRAINT constraint_1 NOT NULL NOT DEFERRABLE;

is that the Table named will increase in size by one Column: the Column defined by the <Column definition>. The <keyword> COLUMN in the ADD [COLUMN] clause is noise and can be omitted. For example, these two SQL statements are equivalent:

ALTER TABLE Table_1 ADD COLUMN
   column_1 SMALLINT DEFAULT 150;

ALTER TABLE Table_1 ADD
   column_1 SMALLINT DEFAULT 150;

Adding a new Column to a Table has a four-fold effect:

1. The degree (i.e.: the number of Columns) of the Table is increased by 1; the new Column’s ordinal position in the Table is the new degree of the Table.

2. Every <AuthorizationID> that has a SELECT, UPDATE, INSERT or REFERENCES Privilege on all existing Columns of the Table receives a matching set of Privileges on the new Column. The grantor of the new Privilege(s) is the same as the grantor of the previous Privileges(s) and so is the grantability of the new Privilege(s).

3. The value of the new Column for every existing row of the Table is set to its default value.

4. The Column is added to the Column list of every UPDATE Trigger event for all Triggers that act on the Table. However, adding a new Column to a Table has no effect on any existing View definition or Constraint definition that refers to the Table because implicit <Column name>s in these definitions are replaced by explicit <Column name>s the first time the View or Constraint is evaluated.

ALTER [ COLUMN ] … SET DEFAULT Clause

The effect of ALTER TABLE <Table name> ALTER [COLUMN] <Column name> SET DEFAULT default value, e.g.:

ALTER TABLE Table_1 ALTER COLUMN
   column_1 SET DEFAULT 200;

is that the default value of the Column named will be changed. (You can use this version of ALTER TABLE either to add a default value to a <Column definition> or to change a Column’s existing default value.) The <keyword> COLUMN in the ALTER [COLUMN] clause is noise and can be omitted. For example, these two SQL statements are equivalent:

ALTER TABLE Table_1 ALTER COLUMN
   column_1 SET DEFAULT CURRENT_TIME;

ALTER TABLE Table_1 ALTER
   column_1 SET DEFAULT CURRENT_TIME;

The ALTER [COLUMN] ... SET DEFAULT options are: DEFAULT <literal>, DEFAULT CURRENT_DATE, DEFAULT CURRENT_TIME(p), DEFAULT CURRENT_TIMESTAMP(p), DEFAULT LOCALTIME(p), DEFAULT LOCALTIMESTAMP(p), DEFAULT USER, DEFAULT CURRENT_USER, DEFAULT SESSION_USER, DEFAULT SYSTEM_USER, DEFAULT CURRENT_PATH, DEFAULT ARRAY[], DEFAULT ARRAY??(??) and DEFAULT NULL – see “<default clause>”, earlier in this chapter.

ALTER [ COLUMN ] … DROP DEFAULT Clause

The effect of ALTER TABLE <Table name> ALTER [COLUMN] <Column name> DROP DEFAULT, e.g.:

ALTER TABLE Table_1 ALTER COLUMN
    column_1 DROP DEFAULT;

is that the default value of the Column named will be removed from the <Column definition>. (You’ll get a syntax error if the Column’s definition doesn’t include a default value.) The <keyword> COLUMN in the ALTER [COLUMN] clause is noise and can be omitted. For example, these two SQL statements are equivalent:

ALTER TABLE Table_1 ALTER COLUMN
   column_1 DROP DEFAULT;

ALTER TABLE Table_1 ALTER
   column_1 DROP DEFAULT;

ALTER [ COLUMN ] … ADD SCOPE Clause

The effect of ALTER TABLE <Table name> ALTER [COLUMN] <Column name> ADD SCOPE <Table name list>, e.g.:

ALTER TABLE Table_2 ALTER COLUMN
   column_1 ADD SCOPE Table_1;

is that a non-empty scope is added to the <Column definition> of the Column named. This version of ALTER TABLE can only be used (a) for Columns with a REF(UDT) <data type>, where the <reference type> descriptor includes an empty scope and (b) where the Column named is not the referenceable Column of its Table. (The Tables named in the SCOPE clause must, of course, be referenceable Base tables whose structured type is the same as the structured type of the referenced UDT.) The <keyword> COLUMN in the ALTER [COLUMN] clause is noise and can be omitted. For example, these two SQL statements are equivalent:

ALTER TABLE Table_3 ALTER COLUMN
   column_1 ADD SCOPE (Table_1,Table_2);

ALTER TABLE Table_3 ALTER
   column_1 ADD SCOPE (Table_1,Table_2);

ALTER [ COLUMN ] … DROP SCOPE clause

The effect of ALTER TABLE <Table name> ALTER [COLUMN] <Column name> DROP SCOPE RESTRICT, e.g.:

ALTER TABLE Table_2 ALTER COLUMN
   column_1 DROP SCOPE RESTRICT;

is that the SCOPE clause in the definition of the Column named becomes empty, provided that no impacted dereference operation is contained in an SQL routine, in a View definition, in a Constraint or Assertion definition or in the triggered action of a Trigger definition. (An impacted dereference operation is a <dereference operation> that operates on the Column named, a <method reference> that operates on the Column named or a <reference resolution> that operates on the Column named.) That is, RESTRICT ensures that only a scope with no dependent Objects can be made empty. If the Column is operated on by any impacted dereference operation, ALTER TABLE ... DROP SCOPE RESTRICT will fail.

The effect of ALTER TABLE <Table name> ALTER [COLUMN] <Column name> DROP SCOPE CASCADE, e.g.:

ALTER TABLE Table_2 ALTER COLUMN
   column_1 DROP SCOPE CASCADE;

is that the SCOPE clause in the definition of the Column named becomes empty and that all Objects which contain an impacted dereference operation for the Column are also dropped, with the CASCADE drop behaviour (except for Assertions, where this is not applicable). This version of ALTER TABLE can only be used (a) for Columns with a REF(UDT) <data type>, where the <reference type> descriptor includes a SCOPE clause and (b) where the Column named is not the referenceable Column of its Table. The <keyword> COLUMN in the ALTER [COLUMN] clause is noise and can be omitted. For example, these two SQL statements are equivalent:

ALTER TABLE Table_2 ALTER COLUMN
   column_1 DROP SCOPE RESTRICT;

ALTER TABLE Table_2 ALTER
   column_1 DROP SCOPE RESTRICT;

DROP [ COLUMN ] Clause

The effect of ALTER TABLE <Table name> DROP [COLUMN] <Column name> RESTRICT, e.g.:

ALTER TABLE Table_1 DROP COLUMN
   column_1 RESTRICT;

is that the Column named is removed from the definition of the Table that owns it, provided that the Column is not referred to in any View definition, SQL routine, Trigger definition or in any Constraint or Assertion definition (with one exception) – and, if the Column is the system-generated Column of its Table, provided that the Table is not named in any SCOPE clause. That is, RESTRICT ensures that only a Column with no dependent Objects can be destroyed. If the Column is used by any other Object, ALTER TABLE ... DROP COLUMN RESTRICT will fail. (Note: A Column referred to in a <Table Constraint> of the Table that owns the Column can be dropped despite the RESTRICT <keyword> if it is the only Column that the <Table Constraint> operates on.) The Column named may not be the only Column in its Table, since a Table must always contain at least one Column. If the Table is a typed Base table, the Column named must be the Table’s referenceable Column.

The effect of ALTER TABLE <Table name> DROP [COLUMN] <Column name> CASCADE, e.g.:

ALTER TABLE Table_1 DROP COLUMN
   column_1 CASCADE;

is that the Column named is removed from the definition of the Table that owns it and that all Objects which are dependent on the Column are also dropped. The <keyword> COLUMN in the DROP [COLUMN] clause is noise and can be omitted. For example, these two SQL statements are equivalent:

ALTER TABLE Table_1 DROP COLUMN
   column_1 RESTRICT;

ALTER TABLE Table_1 DROP
   column_1 RESTRICT;

Dropping a Column from a Table has a six-fold effect:

1. The degree (i.e.: the number of Columns) of the Table is decreased by 1; the ordinal position of each Column that followed this Column in the Table’s definition is adjusted accordingly.

2. The INSERT, UPDATE, SELECT and REFERENCES Privileges on the Column are revoked (by the SQL special grantor, “_SYSTEM”) from the <AuthorizationID> that owns the Column’s Table with a CASCADE drop behaviour, so that the same Privileges are also revoked from all other <AuthorizationID>s.

3. Any Trigger whose definition explicitly includes the Column is dropped and any UPDATE Trigger whose definition includes the Column only implicitly is changed so that it no longer operates on that Column.

4. Any View, Constraint, Assertion or SQL routine whose definition includes the Column is dropped with a CASCADE drop behaviour.

5. If the Column is the system-generated Column of its Table, the Table’s definition is changed so that it no longer shows the Table to be a referenceable Base table and the Table is removed from every SCOPE clause that includes it.

6. The data in the Column is destroyed.

ADD <Table Constraint> Clause

The effect of ALTER TABLE <Table name> ADD <Table Constraint>, e.g.:

ALTER TABLE Table_1 ADD CONSTRAINT
   constraint_1 CHECK(column_1 IS NOT NULL) NOT DEFERRABLE;

is that one <Table Constraint> is added to the definition of the Table named – see “<Table Constraint>” in our chapter on Constraints and Assertions.

DROP CONSTRAINT Clause

The effect of ALTER TABLE <Table name> DROP CONSTRAINT <Constraint name> RESTRICT, e.g.:

ALTER TABLE Table_1 DROP CONSTRAINT constraint_1 RESTRICT;

is that the Constraint named is removed from the definition of the Table that owns it, provided that the Constraint is not used by any SQL routine, and provided that no other Constraint and no View are dependent on the Constraint. (A FOREIGN KEY Constraint is dependent on the UNIQUE or PRIMARY KEY Constraint that names its referenced Columns and a View is dependent on a Constraint if (a) it’s a grouped View that includes a Column which isn’t also referred to in a set function and (b) if the Constraint is needed to conclude that there is a known functional dependency between the group and the Column named.) That is, RESTRICT ensures that only a Constraint with no dependent Objects can be dropped. If the Constraint is used by any other Object, ALTER TABLE ... DROP CONSTRAINT will fail.

The effect of ALTER TABLE <Table name> DROP CONSTRAINT <Constraint name> CASCADE, e.g.:

ALTER TABLE Table_1 DROP CONSTRAINT constraint_1 CASCADE;

is that the Constraint named is removed from the definition of the Table that owns it and that all dependent Constraints, Views and SQL routines are also dropped with a CASCADE drop behaviour. (Note: If the dropped Constraint caused one or more Columns to have the “known not nullable” nullability characteristic, then the affected Columns’ nullability characteristic becomes “possibly nullable” unless some other Constraint also constrains them to non- null values.)

If you want to restrict your code to Core SQL, don’t use ALTER TABLE to drop a Column from a Table, to change a <Column definition> using any of the available options, to add a Constraint to a Table or to drop a Constraint from a Table.

DROP TABLE statement

The DROP TABLE statement destroys a Base table. The required syntax for the DROP TABLE statement is:

DROP TABLE <Table name> {RESTRICT | CASCADE}

DROP TABLE destroys a Base table and its data. The <Table name> must identify an existing Base table whose owner is either the current <AuthorizationID> or a Role that the current <AuthorizationID> may use. That is, only the <AuthorizationID> that owns the Table may drop it. DROP TABLE can be used to drop a persistent Base table, a GLOBAL TEMPORARY Base table or a created LOCAL TEMPORARY Base table, but you can’t use it to drop a declared LOCAL TEMPORARY Base table.

The effect of DROP TABLE <Table name> RESTRICT, e.g.:

DROP TABLE Table_1 RESTRICT;

is that the Table named is destroyed, provided that the Table (a) has no subtables, (b) is not referred to in any View definition, Assertion definition, Trigger definition or SQL routine, (c) is not referred to in any <Table Constraint> that isn’t owned by this Table and (d) doesn’t fall within the scope of any other Table or an SQL parameter. That is, RESTRICT ensures that only a Table with no dependent Objects can be destroyed. If the Table is used by any other Object, DROP TABLE ... RESTRICT will fail.

The effect of DROP TABLE <Table name> CASCADE, e.g.:

DROP TABLE Table_1 CASCADE;

is that the Table named is destroyed.

Successfully destroying a Table has a five-fold effect:

1. The Base table named (and all the data it contains) is destroyed.

2. All subtables of the Table are dropped with a CASCADE drop behaviour.

3. For every supertable of the Table, all superrows of the Table’s rows are deleted and the Table is removed from the supertable’s list of direct subtables.

4. All Privileges held on the Table by the <AuthorizationID> that owns it are revoked (by the SQL special grantor, “_SYSTEM”) with a CASCADE revoke behaviour, so that all Privileges held on the Table by any other <AuthorizationID> are also revoked.

5. All SQL routines, Views, Constraints, Assertions and Triggers that depend on the Table are dropped with a CASCADE drop behaviour.

If you want to restrict your code to Core SQL, don’t use the CASCADE drop behaviour for your DROP TABLE statements.

CREATE VIEW Statement

The CREATE VIEW statement names a new View and defines the query which, when evaluated, determines the rows of data that are shown in the View. The required syntax for the CREATE VIEW statement is:

CREATE [ RECURSIVE ] VIEW <Table name>
[ (<Column name> [ {,<Column name>} ...) ]
AS <query expression>
[ WITH [ {CASCADED | LOCAL} ] CHECK OPTION ]

Name clause:

CREATE VIEW defines a new derived Table, or View. A View is owned by the Schema it belongs to.

• The <Table name> identifies the View and the Schema that it belongs to. A <Table name> that includes an explicit <Schema name> qualifier belongs to the Schema named. A <Table name> that does not include an explicit <Schema name> qualifier belongs to the SQL-session default Schema. The <Table name> must be unique (for all Base tables and Views) within the Schema that owns it.

If CREATE VIEW is part of a CREATE SCHEMA statement, the <Table name>, if explicitly qualified, must include the <Schema name> of the Schema being created; that is, it isn’t possible to create a View belonging to a different Schema from within CREATE SCHEMA. For example, this SQL statement will not return an error because the <Table name> will default to include the qualifying <Schema name>:

CREATE SCHEMA bob
   CREATE TABLE Table_1 (column_1 SMALLINT, column_2 CHAR(5))
   CREATE VIEW View_1 (column_1) AS
      SELECT column_1 FROM Table_1;
   -- creates a View called BOB.VIEW_1 in Schema BOB

This SQL statement will not return an error either because the <Table name> explicitly includes a qualifying <Schema name> that matches the name of the Schema being created:

CREATE SCHEMA bob
   CREATE TABLE bob.Table_1 (
      column_1 SMALLINT, column_2 CHAR(5))
   CREATE VIEW bob.View_1 (column_1) AS
      SELECT column_1 FROM bob.Table_1;
   -- creates a View called BOB.VIEW_1 in Schema BOB

But this SQL statement will return an error because the <Table name> explicitly includes a qualifying <Schema name> that is different from the name of the Schema being created:

CREATE SCHEMA bob
   CREATE TABLE Table_1 (column_1 SMALLINT, column_2 CHAR(5))
   CREATE VIEW sam.View_1 (column_1) AS
      SELECT column_1 FROM bob.Table_1;
   -- tries to create a View belonging to Schema SAM inside Schema BOB; illegal syntax

Privileges

If CREATE VIEW is executed as a stand-alone SQL statement, the current <AuthorizationID> must either be the owner of the Schema that this new View belongs to, or the Schema’s owner must be a Role that the current <AuthorizationID> may use. That is, only the owner of a Schema can create Views for that Schema. The current <AuthorizationID> must also have the SELECT Privilege on every Column used in the View’s query definition.

In addition to creating a View, CREATE VIEW also causes the SQL special grantor, “_SYSTEM”, to grant Privileges on the View to the Schema owner <AuthorizationID> (that is, the <AuthorizationID creating the View). Your Privileges on a View stem from your Privileges on the underlying Tables that make up the View. That is, you get the same Privileges on a View that you hold on the Tables/Columns used in the View’s query definition – with the proviso that if you have the UPDATE, INSERT and/or DELETE Privileges on a Table and you’re creating a non-updatable View, those Privileges will not cascade. So: when you create a View, “_SYSTEM” will grant you Table Privileges (any of INSERT, SELECT, UPDATE, DELETE and REFERENCES that are applicable) on the new View, as well as Column Privileges (any of SELECT, INSERT, UPDATE and REFERENCES that are applicable) on every Column of the new View. If your Privileges on the underlying Tables are grantable, your Privileges on the View will be too.

That is, Views are Tables, but when it comes to Privileges, there are some big differences between the things that happen for Base tables, and the things that happen for Views. The main difference, understandable if you consider that operations on Views are ultimately operations on Base tables, is this: the mere fact that you own a View doesn’t mean that you have ALL PRIVILEGES for that View. Usually you either “inherit” View Privileges (because you possess the Privilege on the underlying Table), or you get them explicitly (because someone GRANTs them to you). So:

• In order to create the View: You must have all Privileges necessary to perform the View’s query on the underlying Tables.

• If you own the View: You have one automatic Privilege – you may destroy it using the DROP VIEW statement.

• If you have the SELECT Privilege on every Column that your View is based on, you get the SELECT Privilege on the View, so you may SELECT from the View. If all of your underlying SELECT Privileges are grantable, so is your SELECT Privilege on the View. You inherit REFERENCES Privileges in the same way: if you have the REFERENCES Privilege on every Column that your View is based on, you get the REFERENCES Privilege on the View, so you may use the View in an Assertion.

• You inherit INSERT, UPDATE and DELETE Privileges in the same way, with a major exception: these Privileges cannot exist if the View is not updatable. That explains why, when you try to update a non-updatable View, the likely error is "Syntax error or access violation" – the ultimate cause is the non-updatability, but the immediate cause at update time is that you lack the appropriate Privilege.

• In addition to the inherited Privileges, you may hold Privileges which are explicitly granted to you. If you don’t own the View, this is the only way to get Privileges on it – you do not hold any Privileges on a View you don’t own merely because you own an underlying Table.

• If your Privilege on an underlying Table is revoked, your Privilege on the View you created using that Privilege is also revoked. This may cause the entire View to be destroyed.

<Column name> Clause

The optional parenthesized <Column name> clause of the CREATE VIEW statement explicitly names the View’s Columns. (As is usual with Tables, each Column must have a name that is unique – for all Columns – within the View.) If you omit this clause, the View’s Columns will inherit the names of the Columns they are based on. For example, consider these two SQL statements:

CREATE TABLE Table_1 (column_1 SMALLINT, column_2 CHAR(5));

CREATE VIEW View_1 AS SELECT column_1 FROM Table_1;

Because the <Column name> clause is omitted, the View’s Column will be named COLUMN_1 – just like the Column it’s based on. Here’s another example:

CREATE TABLE Table_1 (column_1 SMALLINT, column_2 CHAR(5));

CREATE VIEW View_1 (view_column) AS
   SELECT column_1 FROM Table_1;

Because the <Column name> clause is included, the View’s Column will be named VIEW_COLUMN – even though the Column it’s based on has a different name. Note that if you do use the <Column name> clause, you must provide a name for every one of the View’s Columns – it isn’t possible to name some, and allow the others to default.

There are times when you may not omit the <Column name> clause. You must explicitly name a View’s Columns if (a) any of the Columns are derived through the use of a set function, scalar function, <literal> or expression, since none of these have a <Column name> which CREATE VIEW can inherit, (b) the same name would be inherited for more than one of the View’s Columns, usually the case when the View is derived from a join of multiple Tables or (c) when you’re defining a RECURSIVE View (see below). Here’s an example that shows a View definition where the <Column name> clause is mandatory because the second and third Columns have no name to inherit:

CREATE VIEW View_1 (view_column_1, view_column_2, view_column_3) AS
   SELECT   column_1, column_1+25, 'explanation'
   FROM     Table_1;

Note, however, that this CREATE VIEW statement would give you the same result:

CREATE VIEW View_1 AS
   SELECT   column_1 AS view_column_1,
            column_1+25 AS view_column_2,
            'explanation' AS view_column_3
   FROM     Table_1;

that is, you can also use the AS <Column name> clause in the select list of your View query to provide explicit names for the View’s Columns.

AS Clause

The AS clause of the CREATE VIEW statement defines the query that determines the data you’ll see each time you look at the View. At any point in time, a View’s data consists of the rows that result if its query definition were evaluated. If the query is updatable, then your View is an updatable View. Normally, a “query” is a form of SELECT statement (it may be also be VALUES or TABLE; we’ll define “query” more thoroughly in a later chapter), so you can define a View using pretty well any combination of predicates and search conditions. There are, however, some restrictions:

• The query may not contain a host variable or SQL parameter reference.

• The query may not refer to any declared LOCAL TEMPORARY Tables.

• The query may not use an expression which would result in a View Column with a NO COLLATION coercibility attribute.

• The query may not include any references to the View you’re defining unless you’re explicitly defining a RECURSIVE View.

A View’s Columns inherit their <data type> and other attributes and Constraints from the Columns they’re based on.

[Obscure Rule] If a View’s query can’t be represented in INFORMATION_SCHEMA without truncation, your DBMS will return the SQLSTATE warning 0100A "warning-query expression too long for information schema". If you define a View with a query that includes a GROUP BY and/or a HAVING clause that isn’t in a subquery, the View is known as a grouped View.

Macros and Materializers

When materializing a View, your DBMS’s problem is to transform the View’s query definition into a query on the Base table(s) that the View is based on. There are two ways to do this.

The Macro, or Inline, View

The DBMS sees from the View’s query that View V is based on Base table T so it simply replaces all occurrences of V with T, and all occurrences of V’s <Column name>s with T’s <Column name>s. Thus, for example:

SELECT V.column_1 FROM V WHERE V.column_2 = 7;

becomes:

SELECT T.column_1 FROM T WHERE T.column_2 = 7;

This is conceptually the same as the way that an assembler handles a macro, hence the name. A good DBMS will do the entire transformation during the prepare stage, outside the runtime path, so it is very unlikely that a View query will be measurably slower than a query on the underlying Table if a macro transform is possible. However, here’s an example where it’s not possible:

CREATE VIEW View_1 AS
   SELECT g, COUNT(*) AS g_count FROM Table_1 GROUP BY g;

SELECT AVG(g_count) FROM View_1 WHERE g_count = 5;

This SELECT statement can’t work because the macro transform would evaluate to:

SELECT AVG(COUNT(*)) FROM Table_1 WHERE COUNT(*)=5 GROUP BY g;

and that’s not legal SQL syntax.

The Materialized View

The DBMS makes a hidden “temporary Base table” with the same definition as the Columns in the View, and then populates the temporary Table using the View’s query. Thus it would handle our difficult-to-do-with-a-macro View (above) like this:

CREATE LOCAL TEMPORARY TABLE Some_Table_Name (
   g_count INTEGER);

INSERT INTO g_count
   SELECT COUNT(*) FROM Table_1 GROUP BY g;

Now the transform is of the Table expression only, so:

SELECT AVG(g_count) FROM View_1 WHERE g_count = 5;

becomes:

SELECT AVG(g_count) FROM Some_Table_Name;

A materialized View is more flexible and is easier to implement than a macro View because the method of creation is always the same, and because any query expression is transformable. On the negative side of the ledger, it usually takes extra time to populate the temporary Table: the DBMS is not just “selecting from Table A”, it’s “selecting from Table A and putting the results in Table B, then selecting from Table B”. And, if we consider any operation other than SELECT or REFERENCE, we quickly see that the temporary Table is useless – for example, when we INSERT into a View we want the insertion to happen on the actual Table, not on some ephemeral ad-hoc copy of the actual Base table that disappears when we SELECT again. So, we expect that a good DBMS will use macro Views for simple queries and switch to materialized Views when the going gets rough.

Updatable Views

We have seen that, when we SELECT from a View, the DBMS will transform our request into some equivalent request which is a SELECT from the underlying Base table(s). Now, UPDATE or INSERT or DELETE operations (“updates” for short) must also involve a change to the underlying Base tables, otherwise they would be pointless. So, for updates on Views, your DBMS must reverse the transformation. This is often difficult or impossible. The SQL-92 rules for updatability are:

• The query must be a single SELECT ... on a single Table, so Views are not updatable if the SELECT contains select functions (UNION, INTERSECT, EXCEPT, CORRESPONDING) or join operators (JOIN or joining commas as in FROM a,b,c). The query may also be TABLE <Table name> because TABLE <Table name> is, at bottom, a SELECT. Rule 1 is relaxed in SQL3, which has the big effect that you can update Views of joins.

• The select list may contain only <Column name>s and [AS name] clauses. Therefore, this SQL statement defines an updatable View:

CREATE VIEW View_1 AS SELECT a,b,c FROM Table_1;

but these SQL statements do not:

CREATE VIEW View_1 AS SELECT a+5 FROM Table_1;

CREATE VIEW View_1 AS SELECT 'x' FROM Table_1;

CREATE VIEW View_1 AS SELECT a COLLATE polish FROM Table_1;

(These are all theoretically updatable Views, but our main concern here is with what the SQL Standard regards as updatable). Rule 2 is irritating because many arithmetic and string operations are in fact reversible – but the DBMS doesn’t know it.

• There must be no implicit or explicit grouping, so the <keyword>s DISTINCT or GROUP BY or HAVING, or any set function, may not appear in the main query (though they may appear in that query’s subqueries), nor may any subqueries be correlated subqueries (that is, they may not themselves refer to the Table named in the outer query). Rule 3 cannot be gotten around. For example you can’t change the average salary: you have to change the individual salaries (Joke: unless you’re the Canadian Anti-Poverty Commission which once announced that “most Canadians make less than the average wage”). However, the rule is syntax-based – you might find that, in fact, DISTINCT is a no-op (that is, the decision “is it distinct?” is a matter of syntax, not of fact).

• The query may not refer to the View being defined.

• If there are multiple levels of View (that is, Views of Views), the above rules must be followed at every level – that is, if the “single Table” in the query expression is not a Base table, it must be an updatable View. Sometimes, as a very general summation of these rules, people say “a View is updatable only if it represents a subset of the rows and Columns of a single Base table”.

What actually happens when you update a View? Your DBMS performs the operation on the underlying Base table. For these two SQL statements:

DELETE FROM View_1;

UPDATE View_1 SET column_1 = value;

the operation transforms straightforwardly to:

DELETE FROM Table_1;

UPDATE Table SET column_1 = value;

For INSERT operations, there is an additional problem if the View is based on only a subset of the Columns of the underlying Base table. In that case, the rest of the Base table’s Columns all are set to their default value. For example:

CREATE TABLE Table_1 (
   column_1 SMALLINT DEFAULT 12,
   column_2 CHAR(5) DEFAULT 'hello');

CREATE VIEW View_1 (view_column) AS
   SELECT column_1 FROM Table_1;

INSERT INTO View_1 (view_column) VALUES (28);

The result of the INSERT operation on the View is:

INSERT INTO Table_1 (column_1, column_2)
   VALUES (28,'hello');

WITH CHECK OPTION Clause

Consider these two SQL statements:

CREATE VIEW View_1 (view_col_1, view_col_2) AS
   SELECT column_1,column_2 FROM Table_1 WHERE column_1 =5;

UPDATE View_1 SET view_col_1=4;

The View definition restricts it to those rows of TABLE_1 where COLUMN_1 has a value of 5 – but as soon as the UPDATE operation succeeds, there will be no such rows. To the user, the apparent effect is that all the rows of VIEW_1 “disappear” – as if they were deleted instead of updated. That is okay and legal, but doesn’t it contradict the View idea? That is, if someone is restricted during SELECT to finding only those rows that match the condition column_1=5, why should he/she/it be allowed to UPDATE or INSERT rows that do not follow the same restriction?

The solution is to use the optional WITH CHECK OPTION clause in your updatable View definitions (it is valid only if you are defining an updatable View). Adjust the two SQL statements to:

CREATE VIEW View_1 (view_col_1, view_col_2) AS
   SELECT column_1,column_2 FROM Table_1 WHERE column_1 =5
   WITH CHECK OPTION;

UPDATE View_1 SET view_col_1=4;

and now the UPDATE statement will fail: your DBMS will return the SQLSTATE error 44000 "with check option violation". The effect of WITH CHECK OPTION is to say: “the WHERE clause defines what is in the View and you cannot go outside the bounds of that definition in any INSERT or UPDATE operation”.

A View’s CHECK OPTION is effectively a Constraint. That is, there is a similarity between the two definitions in this example:

-- Constraint on Base table
CREATE TABLE Table_1 (
   column_1 INT,
   CHECK (column_1<5000));

INSERT INTO Table_1 VALUES(6000);
-- results in error

-- Constraint on View
CREATE TABLE Table_1 (column_1 INT);

CREATE VIEW View_1 AS
   SELECT column_1 FROM Table_1  WHERE column_1<5000;

INSERT INTO View_1 VALUES(6000);
-- results in error

If WITH CHECK OPTION is included in a View definition, then all INSERT and UPDATE operations on that View will be checked to ensure that every new row satisfies the View’s query conditions. Such “View Constraints” are popular, probably because the CREATE VIEW statement has been around for a longer time than the Base table CHECK Constraint. There are a few downsides you should be aware of, though, if you use such View Constraints:

• You do not have the option of deferring Constraint checking. All checks happen at the end of the INSERT or UPDATE operation.

• It is somewhat easier to violate a View Constraint than a proper Table Constraint.

• The SQL Standard does not make it clear what the effect should be if NULLs are used (and therefore make the result of the Constraint check UNKNOWN rather than TRUE or FALSE). For the Base table insertion:

INSERT INTO Table_1 VALUES (NULL);

the answer is clear: there is no error and so the INSERT is allowed. Presumably the same is true for the View insertion:

INSERT INTO View_1 VALUES (NULL);

but without a guarantee, why risk it?

Regardless of the reasons you use WITH CHECK OPTION – as a general constrainer or as an encapsulation enforcer – the View must obey these rules:

• The WHERE clause must be “deterministic” (this only means that you can’t use Columns which might change in value, such as CURRENT_TIME. For a more complete explanation, see our chapter on Constraints and Assertions.

• The View must be updatable.

Suppose you create Views within Views within Views, for example:

CREATE TABLE TABLE_1 (
   column_1 INTEGER,
   column_2 INTEGER,
   column_3 INTEGER,
   column_4 INTEGER,
   column_5 INTEGER);

CREATE VIEW View_1 AS
   SELECT * FROM Table_1 WHERE column_1 <> 0;

CREATE VIEW View_2 AS
   SELECT * FROM View_1 WHERE column_2 <> 0
   WITH CASCADED CHECK OPTION;

CREATE VIEW View_3 AS
   SELECT * FROM View_2 WHERE column_3 <> 0;

CREATE VIEW View_4 AS
   SELECT * FROM View_3 WHERE column_4 <> 0
   WITH LOCAL CHECK OPTION;

CREATE VIEW View_5 AS
   SELECT * FROM View_4 WHERE column_5 <> 0;

Note

A Table used in the definition of another Table is an “immediately underlying” Table; thus Table TABLE_1 underlies View VIEW_1. Because VIEW_1 is then used in the definition of VIEW_2, we say that VIEW_1 immediately underlies VIEW_2 and that TABLE_1 indirectly underlies VIEW_2. Taken together, both TABLE_1 and VIEW_1 “generally underlie” VIEW_2: one indirectly and the other immediately.

Our example contains a variety of WITH CHECK OPTION clauses. To see what their effects are, we will try to do an INSERT for each of the Views. We begin with VIEW_1:

INSERT INTO View_1 VALUES (0,0,0,0,0);

This INSERT operation is legal: there is no check option in the View’s definition, so the row is inserted into TABLE_1 despite the fact that we won’t be able to see that effect by looking at VIEW_1.

Here’s an INSERT into VIEW_2:

INSERT INTO View_2 VALUES (1,0,1,1,1);

This INSERT operation will fail: there is a check option saying that the second column may not be 0. No surprise there, but this will fail too!:

INSERT INTO View_2 VALUES (0,1,1,1,1);

When you define a View with WITH CASCADED CHECK OPTION (or with WITH CHECK OPTION) they mean the same thing because CASCADED is the default), then the check applies not only to the View you’re updating, but to every View that underlies it – and VIEW_1 contains a condition that disallows zeros in COLUMN_1.

Here are two INSERT``s into ``VIEW_3:

INSERT INTO View_3 VALUES (0,1,1,1,1);

INSERT INTO View_3 VALUES (1,0,1,1,1);

These INSERT operations will also fail: although VIEW_3 has no check option, its underlying Views do and so operations on VIEW_3 may not violate their conditions. But, since VIEW_3 has no check option of its own, this INSERT is legal:

INSERT INTO View_3 VALUES (1,1,0,1,1);

because there is no check on VIEW_3, or on its underlying Views, that disallows zeros in COLUMN_3.

Now, here’s an INSERT into VIEW_4:

INSERT INTO View_4 VALUES (0,0,0,1,1);

This INSERT operation is legal. For VIEW_4, there is only one check condition in effect – that COLUMN_4 may not be zero – and this condition is satisfied by the INSERT. VIEW_3’s condition – that COLUMN_3 may not be zero – doesn’t have to be satisfied because VIEW_4 was defined with WITH LOCAL CHECK OPTION. This means that, while VIEW_4’s condition will be checked, the conditions of its immediately underlying View will only be checked if that View was defined with a WITH CHECK OPTION clause (which is not the case for VIEW_3). It also means that the conditions of its indirectly underlying Views (that is, of VIEW_1 and VIEW_2) won’t be checked at all, regardless of their WITH CHECK OPTION definitions.

Finally, here’s an INSERT into VIEW_5:

INSERT INTO View_5 VALUES (0,0,0,1,0);

Once again, this INSERT operation is legal: VIEW_5 has no WITH CHECK OPTION clause, and so its condition is not checked. However, the View it’s based on (VIEW_4) does have a WITH CHECK OPTION clause, and so this INSERT statement will fail:

INSERT INTO View_5 VALUES (1,1,1,0,1);

because VIEW_4’s condition is checked for any INSERT or UPDATE operation on VIEW_5. But, since VIEW_4 was defined with WITH LOCAL CHECK OPTION, the View immediately underlying VIEW_4 is the only other View whose conditions will be checked – and then only if that View was defined with a WITH CHECK OPTION clause. Thus, for this example, operations on VIEW_5 are affected only by the conditions set for VIEW_4 – COLUMN_1 may be zero, COLUMN_2 may be zero and COLUMN_3 may be zero because the conditions for VIEW_1, VIEW_2 and VIEW_3 don’t apply.

Our example is fairly complex because we mixed all the possibilities together. In real life, one avoids the complexity by declaring a company policy: always use WITH CASCADED CHECK OPTION.

RECURSIVE Views

Here is a contrived, illegal example of two Views that reference each other:

CREATE SCHEMA some_schema
   CREATE TABLE Table_1 (column_1 INTEGER)
   CREATE VIEW View_1 (view_1_col_1,view_1_col_2,view_1_col_3) AS
      SELECT column_1,view_2_col_1,'A' FROM Table_1,View_2
   CREATE VIEW View_2 (view_2_col_1,view_2_col_2,view_2_col_3) AS
      SELECT view_1_col_1,column_1,'B' FROM Table_1,View_1;

Although the definition of VIEW_1 is legal, the definition of VIEW_2 is not because it defines a recursive View: a View whose query refers to the View being defined – that is, since VIEW_2 is based on VIEW_1, and since VIEW_1 is based on VIEW_2, ultimately, VIEW_2 is based on itself. We used the CREATE SCHEMA statement for this example because, within CREATE SCHEMA, each Column in each View derives ultimately from either a Base table Column or a <literal>, so recursive Views are possible in theory. In reality, though, current DBMSs don’t allow them and neither does the SQL-92 Standard. In SQL3, though, recursion is allowed provided it’s explicit and provided that CREATE VIEW includes a <Column name> clause but no WITH CHECK OPTION clause. Thus, with SQL3 only, this SQL statement is possible:

CREATE SCHEMA some_schema
   CREATE TABLE Table_1 (column_1 INTEGER)
   CREATE VIEW View_1 (view_1_col_1,view_1_col_2,view_1_col_3) AS
      SELECT column_1,view_2_col_1,'A' FROM Table_1,View_2
   CREATE RECURSIVE VIEW View_2 (view_2_col_1,view_2_col_2,view_2_col_3) AS
      SELECT view_1_col_1,column_1,'B' FROM Table_1,View_1;

If you want to restrict your code to Core SQL, don’t define any RECURSIVE Views, don’t use the EXCEPT, INTERSECT or CORRESPONDING operators in your View queries and don’t use the optional CASCADED or LOCAL levels specification in your check clauses – always define Views only with WITH CHECK OPTION alone.

Getting More Out Of Views

A View’s virtue is that it isn’t the whole picture. Something is hidden. Hidden is good. In the following descriptions, which all show Views being put to some useful purpose, the same refrain could be sung each time: “Views Hide – Good”.

One thing we’d like to hide, or abstract, is the retrieval method. For a mailing list, the method might look like this:

SELECT Customers.given,
       Customers.surname,
       Customers.city,
       Customers.address
FROM   Customers
UNION
SELECT Suppliers.given,
       Suppliers.surname,
       Suppliers.city,
       Suppliers.address
FROM Suppliers;

Well, some people, sometimes, need to know that the mailing list is a union of data from two Tables. But when you print your list, all you really want to say is:

SELECT given, surname, city, address FROM Mailouts;

This is not a mere matter of “reducing keystrokes”. The idea is to remove information which is not necessary for the task at hand.

Another thing we want to hide is details. For example, there are some people who only care about department-level sales figures, as opposed to individual sales. For them we want to say:

CREATE VIEW View_1 AS
   SELECT SUM(sales_amount), manager
   FROM Sales
   GROUP BY manager;

… and they can select whatever they like from VIEW_1 (which is an example of a “grouped View”).

The next thing we want to hide is secrets. We could grant PUBLIC access to some grouped Views, or we could grant PUBLIC access to only certain Columns in certain Tables. In fact, granting on Column-subset Views is the normal way to do a by-Column GRANT (see our chapter on AuthorizationIDs).

Tip

Make a one-row permanent Table with:

CREATE VIEW View_name AS VALUES (1,2,3);

To create one-row Tables which you never intend to update, this is more convenient than using the CREATE TABLE statement because you can forget about file storage. If your DBMS doesn’t allow the use of the <keyword> VALUES for a View’s <query expression>, make your View with a selection from a one-row Table in INFORMATION_SCHEMA, e.g.:

CREATE VIEW View_name AS
   SELECT 1,2,3 FROM INFORMATION_SCHEMA.SQL_LANGUAGES;

DROP VIEW Statement

The DROP VIEW statement destroys a View. The required syntax for the DROP VIEW statement is:

DROP VIEW <Table name> {RESTRICT | CASCADE}

DROP VIEW destroys a View, but does not destroy any data: the data in the underlying Tables will remain. The <Table name> must identify an existing View whose owner is either the current <AuthorizationID> or a Role that the current <AuthorizationID> may use. That is, only the <AuthorizationID> that owns the View may drop it.

The effect of DROP VIEW <Table name> RESTRICT, e.g.:

DROP VIEW View_1 RESTRICT;

is that the View named is destroyed, provided that the View is not referred to in any other View definition and is not referred to in any Constraint definition, Assertion definition, Trigger definition or SQL routine. That is, RESTRICT ensures that only a View with no dependent Objects can be destroyed. If the View is used by any other Object, DROP VIEW ... RESTRICT will fail.

The effect of DROP VIEW <Table name> CASCADE, e.g.:

DROP VIEW View_1 CASCADE;

is that the View named is destroyed.

Successfully destroying a View has a three-fold effect:

1. The View named is destroyed.

2. All Privileges held on the View by the <AuthorizationID> that owns it are revoked (by the SQL special grantor, “_SYSTEM”) with a CASCADE revoke behaviour, so that all Privileges held on the View by any other <AuthorizationID> are also revoked.

3. All SQL routines, Views, Constraints, Assertions and Triggers that depend on the View are dropped with a CASCADE drop behaviour.

If you want to restrict your code to Core SQL, don’t use the CASCADE drop behaviour for your DROP VIEW statements.

DECLARE TABLE Statement

The DECLARE TABLE statement names a new declared local temporary Base table and defines the Table’s Columns and Constraints. The required syntax for the DECLARE TABLE statement is:

DECLARE LOCAL TEMPORARY TABLE [ MODULE. ]<Table name>
   (<table element> [ {,<table element>}... ])
   [ ON COMMIT {PRESERVE ROWS | DELETE ROWS} ]

         <table element> ::=
         <Column definition> |
         <Table Constraint> |
         LIKE <Table name> |
         <Column name> WITH OPTIONS <column option list>

DECLARE LOCAL TEMPORARY TABLE defines a new declared local temporary Base table. You can only use this SQL statement within a MODULE statement. Declared temporary Tables aren’t part of the Table metadata in INFORMATION_SCHEMA.

In effect, a declared local temporary Table does not exist until it is invoked by an SQL-client Module during an SQL-session. Once invoked, it will only be visible to the Module in which it is declared – it will not be visible to other users. At the end of the SQL-session, all declared temporary Tables invoked during the SQL-session are dropped.

• The <Table name> identifies the Table and the Module that it belongs to and must be unique (for all declared local temporary Base tables) within the Module that owns it. Because a declared local temporary Table is distinct within an SQL-client Module within an SQL-session, the Schema it belongs to is a Schema determined by your DBMS, so don’t add a qualifying <Schema name> when you declare a temporary Table. (In effect, your DBMS will fix a qualifying <Schema name> for a declared local temporary Table based on the DBMS’s name for the SQL-session in which you invoke that Table, coupled with its name for the SQL-client Module that contains that Table’s declaration.) Whenever you refer to a declared local temporary Table, you must preface the <Table name> with MODULE.

The <table element> clause defines the structure of the Table’s contents: it tells you what sort of data the Table contains. This clause is contains a list of table elements, such as Column and Table Constraint definitions, that are just like the elements we described for the CREATE TABLE statement – see those descriptions for detailed information. Every temporary Table declaration has to contain at least one <Column definition>. Here are two equivalent examples:

DECLARE LOCAL TEMPORARY TABLE MODULE.Table_1 (
   column_1 SMALLINT,
   column_2 DATE,
   column_3 VARCHAR(25),
   CONSTRAINT constraint_1 UNIQUE(column_1)
   CONSTRAINT constraint_2 CHECK(column_3 IS NOT NULL));

DECLARE LOCAL TEMPORARY TABLE Table_1 (
   column_1 SMALLINT,
   column_2 DATE,
   column_3 VARCHAR(25),
   CONSTRAINT constraint_1 UNIQUE(column_1)
   CONSTRAINT constraint_2 CHECK(column_3 IS NOT NULL));

When you declare a local temporary Table, you may use the ON COMMIT clause to specify whether you want the Table to be emptied whenever a COMMIT statement is executed. If you omit the ON COMMIT clause from DECLARE LOCAL TEMPORARY TABLE, it defaults to ON COMMIT DELETE ROWS. For example, these two SQL statements are equivalent:

DECLARE LOCAL TEMPORARY TABLE Table_1 (
   column_1 SMALLINT);

DECLARE LOCAL TEMPORARY TABLE Table_1 (
   column_1 SMALLINT)
   ON COMMIT DELETE ROWS;

Based on this Table declaration, the effect of these two SQL statements:

INSERT INTO MODULE.Table_1 (column_1) VALUES(10);

COMMIT;

is that TABLE_1 is first materialized and has data inserted into it, and then the rows are deleted. That is, at COMMIT time, your DBMS effectively executes this SQL statement:

DELETE FROM Table_1;

since the declaration of TABLE_1 states that the Table is to be emptied at COMMIT. On the other hand, the effect of these three SQL statements:

DECLARE LOCAL TEMPORARY TABLE Table_1 (
   column_1 SMALLINT)
   ON COMMIT PRESERVE ROWS;

INSERT INTO MODULE.Table_1 (column_1) VALUES(10);

COMMIT;

is that TABLE_1 is declared, materialized and has data inserted into it, and then the rows are committed. That is, at COMMIT time, your DBMS does not delete the rows, since TABLE_1’s declaration explicitly says not to. (The rows will, however, be deleted at the end of the SQL-session.)

In addition to declaring a Table, DECLARE LOCAL TEMPORARY TABLE also causes the SQL special grantor, “_SYSTEM”, to grant non-grantable INSERT, SELECT, UPDATE, DELETE and REFERENCES Privileges on the declared Table, as well as non-grantable SELECT, INSERT, UPDATE and REFERENCES Privileges on every Column in the declared Table, to the current <AuthorizationID>. This ensures that the Table may be materialized, and operated on, by any <AuthorizationID> that can run the Module that contains the Table declaration.

If you want to restrict your code to Core SQL, don’t use the DECLARE LOCAL TEMPORARY TABLE statement.

Dialects

Some vendors relax the rules on what may be an updatable View. For example, IBM’s DB2 will let you DELETE from Views whose queries contain arithmetic expressions, and will even let you UPDATE such Views provided you don’t try to update the Column derived from the expression.

Although the Full-SQL syntax for the check option clause is WITH [ {CASCADED|LOCAL} ] CHECK OPTION, the majority of vendors only allow the optionless syntax WITH CHECK OPTION, which is all that’s required for Core SQL. In general, the check option is always CASCADED (as required by the Standard), but there was a time when the default was LOCAL so some caution is necessary. Sometimes WITH CHECK OPTION may be illegal even though the View is updatable; for example IBM’s DB2 once insisted that there could be no subqueries in the View definition.