| 4.14 Tables |
| |
| A table is a collection of rows having one or more columns. A row is a value of a row type. Every row of the same table has the same row type. The value of the i-th field of every row in a table is the value of the i-th column of that row in the table. The row is the smallest unit of data that can be inserted into a table and deleted from a table. |
| |
| The degree of a table, and the degree of each of its rows, is the number of columns of that table. The number of rows in a table is its cardinality. A table whose cardinality is 0 (zero) is said to be empty. |
| |
| A table is either a base table, a derived table, or a transient table. A base table is either a persistent base table, a global temporary table, a created local temporary table, or a declared local temporary table. |
| |
| A persistent base table is a named table defined by a <table definition> that does not specify TEMPORARY . A derived table is a table derived directly or indirectly from one or more other tables by the evaluation of a <query expression> whose result has an element type that is a row type. The values of a derived table are derived from the values of the underlying tables when the <query expression> is evaluated. A viewed table is a named derived table defined by a <view definition> . A viewed table is sometimes called a view. |
| |
| A transient table is a named table that may come into existence implicitly during the evaluation of a <query expression> or the execution of a trigger. A transient table is identified by a <query name> if it arises during the evaluation of a <query expression> , or by a <transition table name> if it arises during the execution of a trigger. Such tables exist only for the duration of the executing SQL-statement containing the <query expression> or for the duration of the executing trigger. |
| |
| All base tables are updatable. Every column of a base table is an updatable column. Derived tables and transient tables are either updatable or not updatable. The operations of update and delete are permitted for updatable tables, subject to constraining Access Rules. Some updatable tables, including all base tables whose row type is not derived from a user-defined type that is not instantiable, are also insertable-into, in which case the operation of insert is also permitted, again subject to Access Rules. |
| |
| A table T2 is part of a column C of a table T1 if setting the value of T1.C to a null value (ignoring any constraints or triggers defined on T1 or T1.C) would cause T2 to disappear. The most specific type of a row is a row type. All rows of a table are of the same row type and this is called the row type of that table. |
| |
| A table is described by a table descriptor. A table descriptor is either a base table descriptor, a view descriptor, or a derived table descriptor (for a derived table that is not a view). |
| Every table descriptor includes: |
| - The column descriptor of each column in the table. |
| - The name, if any, of the structured type, if any, associated with the table. |
| - An indication of whether the table is insertable-into or not. |
| - An indication of whether the table is a referenceable table or not, and an indication of whether the selfreferencing column is a system-generated, a user-generated, or a derived self-referencing column. |
| - A list, possibly empty, of the names of its direct supertables. |
| - A list, possibly empty, of the names of its direct subtables. |
| |
| A transient table descriptor describes a transient table. In addition to the components of every table descriptor, a transient table descriptor includes: |
| - If the transient table is defined by a <with list element> contained in a <query expression> , then the <query name> . If the transient table is defined by a <trigger definition> then the <transition table name> . |
| |
| A base table descriptor describes a base table. In addition to the components of every table descriptor, a base table descriptor includes: |
| - The name of the base table. |
| - An indication of whether the table is a persistent base table, a global temporary table, a created local temporary table, or a declared local temporary table. |
| - If the base table is a global temporary table, a created local temporary table, or a declared local temporary table, then an indication of whether ON COMMIT PRESERVE ROWS was specified or ON COMMIT DELETE ROWS was specified or implied. |
| - The descriptor of each table constraint specified for the table. |
| - A non-empty set of functional dependencies, according to the rules given in Subclause 4.18, "Functional dependencies". |
| - A non-empty set of candidate keys, according to the rules of Subclause 4.19, "Candidate keys". |
| - A preferred candidate key, which may or may not be additionally designated the primary key, according to the Rules in Subclause 4.18, "Functional dependencies". |
| |
| A derived table descriptor describes a derived table. In addition to the components of every table descriptor, a derived table descriptor includes: |
| - The <query expression> that defines how the table is to be derived. |
| - An indication of whether the derived table is updatable or not. |
| |
| A view descriptor describes a view. In addition to the components of a derived table descriptor, a view descriptor includes: |
| - The name of the view. |
| - An indication of whether the view has the CHECK OPTION ; if so, whether it is to be applied as CASCADED or LOCAL. |
| - The original <query expression> of the view. |
| |
