| <html> |
| <head> |
| <META http-equiv="Content-Type" content="text/html; charset=UTF-8"> |
| <title>Chapter 4. Schemas and Database Objects</title> |
| <link href="../docbook.css" rel="stylesheet" type="text/css"> |
| <meta content="DocBook XSL-NS Stylesheets V1.74.0" name="generator"> |
| <meta name="keywords" content="Hsqldb, HyperSQL, SQL"> |
| <meta name="keywords" content="Hsqldb, HyperSQL, Database, JDBC, Java"> |
| <link rel="home" href="index.html" title="HyperSQL User Guide"> |
| <link rel="up" href="index.html" title="HyperSQL User Guide"> |
| <link rel="prev" href="sessions-chapt.html" title="Chapter 3. Sessions and Transactions"> |
| <link rel="next" href="texttables-chapt.html" title="Chapter 5. Text Tables"> |
| </head> |
| <body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"> |
| <div class="navheader"> |
| <table summary="Navigation header" width="100%"> |
| <tr> |
| <td align="left" width="30%"><a accesskey="p" href="sessions-chapt.html"><img src="../images/db/prev.png" alt="Prev"></a> </td><td align="center" width="40%" style="font-weight:bold;">Chapter 4. Schemas and Database Objects</td><td align="right" width="30%"> <a accesskey="n" href="texttables-chapt.html"><img src="../images/db/next.png" alt="Next"></a></td> |
| </tr> |
| <tr> |
| <td valign="top" align="left" width="30%">Chapter 3. Sessions and Transactions </td><td align="center" width="40%"><a accesskey="h" href="index.html"><img src="../images/db/home.png" alt="Home"></a></td><td valign="top" align="right" width="30%"> Chapter 5. Text Tables</td> |
| </tr> |
| </table> |
| </div> |
| <HR> |
| <div class="chapter" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title"> |
| <a name="databaseobjects-chapt"></a>Chapter 4. Schemas and Database Objects</h2> |
| </div> |
| <div> |
| <div class="authorgroup"> |
| <div class="author"> |
| <h3 class="author"> |
| <span class="firstname">Fred</span> <span class="surname">Toussi</span> |
| </h3> |
| <div class="affiliation"> |
| <span class="orgname">The HSQL Development Group<br> |
| </span> |
| </div> |
| </div> |
| </div> |
| </div> |
| <div> |
| <p class="releaseinfo">$Revision: 3622 $</p> |
| </div> |
| <div> |
| <div class="legalnotice"> |
| <a name="N10AC4"></a> |
| <p>Copyright 2009 Fred Toussi. Permission is granted to distribute |
| this document without any alteration under the terms of the HSQLDB |
| license. Additional permission is granted to the HSQL Development Group |
| to distribute this document with or without alterations under the terms |
| of the HSQLDB license.</p> |
| </div> |
| </div> |
| <div> |
| <p class="pubdate">$Date: 2010-06-04 11:33:51 -0400 (Fri, 04 Jun 2010) $</p> |
| </div> |
| </div> |
| </div> |
| <div class="toc"> |
| <p> |
| <b>Table of Contents</b> |
| </p> |
| <dl> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10AC7">Overview</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10AD2">Schemas and Schema Objects</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10AF8">Names and References</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10B18">Character Sets</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10B32">Collations</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10B3F">Distinct Types</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10B46">Domains</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10B4D">Number Sequences</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10BA3">Tables</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10BAF">Views</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10BCD">Constraints</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10C26">Assertions</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10C30">Triggers</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10C3E">Routines</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10C57">Indexes</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10C5C">Statements for Schema Definition and Manipulation</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10C61">Common Elements and Statements</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10CE1">Renaming Objects</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10D01">Commenting Objects</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10D1D">Schema Creation</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N10D6E">Table Creation and Manipulation</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N110DE">View Creation and Manipulation</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N1113C">Domain Creation and Manipulation</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N111D1">Trigger Creation</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N1124D">Routine Creation</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N112F5">Sequence Creation</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N1137F">SQL Procedure Statement</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N1139F">Other Schema Object Creation</a></span> |
| </dt> |
| </dl> |
| </dd> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N114B8">The Information Schema</a></span> |
| </dt> |
| <dd> |
| <dl> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N114C3">Predefined Character Sets, Collations and Domains</a></span> |
| </dt> |
| <dt> |
| <span class="section"><a href="databaseobjects-chapt.html#N114CE">Views in INFORMATION SCHEMA</a></span> |
| </dt> |
| </dl> |
| </dd> |
| </dl> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N10AC7"></a>Overview</h2> |
| </div> |
| </div> |
| </div> |
| <p>The persistent elements of an SQL environment are database objects. |
| The database consists of catalogs plus authorizations.</p> |
| <p>A catalog contains schemas, while schemas contain the objects that |
| contain data or govern the data.</p> |
| <p>Each catalog contains a special schema called INFORMATION_SCHEMA. |
| This schema is read-only and contains some views and other schema objects. |
| The views contain lists of all the database objects that exist within the |
| catalog, plus all authorizations.</p> |
| <p>Each database object has a name. A name is an identifier and is |
| unique within its name-space.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N10AD2"></a>Schemas and Schema Objects</h2> |
| </div> |
| </div> |
| </div> |
| <p>In HyperSQL, there is only one catalog per database. The name of the |
| catalog is PUBLIC. You can rename the catalog with the <code class="literal">ALTER |
| CATALOG RENAME TO</code> statement. All schemas belong the this |
| catalog. The catalog name has no relation to the file name of the |
| database.</p> |
| <p>Each database has also an internal "unique" name which is |
| automatically generated when the database is created. This name is used |
| for event logging. You can also change this unique name.</p> |
| <p>Schema objects are database objects that contain data or govern or |
| perform operations on data. By definition, each schema object belongs to a |
| specific schema.</p> |
| <p>Schema objects can be divided into groups according to their |
| characteristics.</p> |
| <div class="itemizedlist"> |
| <ul type="disc"> |
| <li> |
| <p>Some kinds of schema objects can exist independently from other |
| schema object. Other kinds can exist only as an element of another |
| schema object. These dependent objects are automatically destroyed |
| when the parent object is dropped.</p> |
| </li> |
| <li> |
| <p>Separate name-spaces exists for different kinds of schema |
| object. Some name-spaces are shared between two similar kinds of |
| schema objects.</p> |
| </li> |
| <li> |
| <p>There can be dependencies between various schema objects, as a |
| schema object can include references to other schema objects. These |
| references can cross schema boundaries. Interdependence and cross |
| referencing between schema objects is allowed in some circumstances |
| and disallowed in some others.</p> |
| </li> |
| <li> |
| <p>Schema objects can be destroyed with the DROP statement. If |
| dependent schema objects exist, a DROP statement will succeed only if |
| it has a CASCADE clause. In this case, dependent objects are also |
| destroyed in most cases. In some cases, such as dropping DOMAIN |
| objects, the dependent objects are not destroyed, but modified to |
| remove the dependency.</p> |
| </li> |
| </ul> |
| </div> |
| <p>A new HyperSQL catalog contains an empty schema called PUBLIC. By |
| default, this schema is the initial schema when a new session is started. |
| New schemas and schema objects can be defined and used in the PUBLIC |
| schema, as well as any new schema that is created by the user. You can |
| rename the PUBLIC schema.</p> |
| <p>HyperSQL allows all schemas to be dropped, except the schema that is |
| the default initial schema for new sessions (by default, the PUBLIC |
| schema). For this schema, a DROP SCHEMA ... CASCADE statement will succeed |
| but will result in an empty schema, rather than no schema.</p> |
| <p>The statements for setting the initial schema for users are |
| described in the <a class="link" href="accesscontrol-chapt.html" title="Chapter 6. Access Control">Statements for |
| Authorization and Access Control</a> chapter.</p> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10AF8"></a>Names and References</h3> |
| </div> |
| </div> |
| </div> |
| <p>The name of a schema object is an |
| <code class="literal"><identifier></code>. The name belongs to the |
| name-space for the particular kind of schema object. The name is unique |
| within its name-space. For example, each schema has a separate |
| name-space for TRIGGER objects.</p> |
| <p>In addition to the name-spaces in the schema. Each table has a |
| name-space for the names of its columns.</p> |
| <p>Because a schema object is always in a schema and a schema always |
| in a catalog, it is possible, and sometimes necessary, to qualify the |
| name of the schema object that is being referenced in an SQL statement. |
| This is done by forming an <<code class="literal">identifier chain></code>. |
| In some contexts, only a simple <code class="literal"><identifier></code> |
| can be used and the <code class="literal"><identifier chain></code> is |
| prohibited. While in some other contexts, the use of |
| <code class="literal"><identifier chain></code> is optional. An identifier |
| chain is formed by qualifying each object with the name of the object |
| that owns its name-space. Therefore a column name is prefixed with a |
| table name, a table name is prefixed with a schema name, and a schema |
| name is prefixed with a catalog name. A fully qualified column name is |
| in the form <code class="literal"><catalog name>.<schema name>.<table |
| name>.<column name></code>, likewise, a fully qualified |
| sequence name is in the form <code class="literal"><catalog name>.<schema |
| name>.<sequence name></code>.</p> |
| <p>HyperSQL extends the SQL standard to allow renaming all database |
| objects. The ALTER ... RENAME TO command has slightly different forms |
| depending on the type of object. If an object is referenced in a VIEW or |
| ROUTINE definition, it is not always possible to rename it.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10B18"></a>Character Sets</h3> |
| </div> |
| </div> |
| </div> |
| <p>A CHARACTER SET is the whole or a subset of the UNICODE |
| character set.</p> |
| <p>A character set name can only be a <code class="literal"><regular |
| identifier></code>. There is a separate name-space for character |
| sets.</p> |
| <p>There are several predefined character sets. These character |
| sets belong to INFORMATION_SCHEMA. However, when they are referenced in |
| a statement, no schema prefix can be used in the statement that |
| references them.</p> |
| <p>The following character sets have been specified by the SQL |
| Standard:</p> |
| <p>SQL_TEXT, SQL_IDENTIFIER, SQL_CHARACTER, ASCII_GRAPHIC, |
| GRAPHIC_IRV, ASCII_FULL, ISO8BIT, LATIN1, UTF32, UTF16, UTF8.</p> |
| <p>The ASCII_GRAPHIC is the same as GRAPHIC_IRV and ASCII_FULL is |
| the same as ISO8BIT.</p> |
| <p>Most of the character sets are defined by well-known standards |
| such as UNICODE.</p> |
| <p>The SQL_CHARACTER consists of ASCII letters, digits and the |
| symbols used in the SQL language. The SQL_TEXT, SQL_IDENTIFIER are |
| implementation defined. HyperSQL defines SQL_TEXT as the UNICODE |
| character set and SQL_IDENTIFIER as the UNICODE character set minus the |
| SQL language special characters.</p> |
| <p>The character repertoire of HyperSQL is the UTF16 character |
| set, which covers all possible character sets. If a predefined character |
| set is specified for a table column, then any string stored in the |
| column must contain only characters from the specified character |
| set.</p> |
| <p>Early releases of HyperSQL version 2.0 may not enforce the |
| CHARACTER SET that is specified for a column and may accept any |
| character string.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10B32"></a>Collations</h3> |
| </div> |
| </div> |
| </div> |
| <p>A COLLATION is the method used for ordering character strings |
| in ordered sets and to determine equivalence of two character |
| strings.</p> |
| <p>There are several predefined collations. These collations |
| belong to INFORMATION_SCHEMA. However, when they are referenced in a |
| statement, no schema prefix can be used in the statement that references |
| them.</p> |
| <p>There is a separate name-space for collations..</p> |
| <p>Collations for a large number of languages are supported by |
| HyperSQL.</p> |
| <p>Early releases of HyperSQL version 2.0 only support a single |
| collation for the whole database.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10B3F"></a>Distinct Types</h3> |
| </div> |
| </div> |
| </div> |
| <p>A distinct, user-defined TYPE is simply based on a built-in |
| type. A distinct TYPE is used in table definitions and in CAST |
| statements.</p> |
| <p>Distinct types share a name-space with domains.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10B46"></a>Domains</h3> |
| </div> |
| </div> |
| </div> |
| <p>A DOMAIN is a user-defined type, simply based on a built-in |
| type. A DOMAIN can have constraints that limit the values that the |
| DOMAIN can represent. A DOMAIN can be used in table definitions and in |
| CAST statements.</p> |
| <p>Distinct types share a name-space with domains.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10B4D"></a>Number Sequences</h3> |
| </div> |
| </div> |
| </div> |
| <p>A SEQUENCE object produces INTEGER values in sequence. The |
| SEQUENCE can be referenced in special contexts only within certain SQL |
| statements. For each row where the object is referenced, its value is |
| incremented.</p> |
| <p>There is a separate name-space for SEQUENCE objects.</p> |
| <p>IDENTITY columns are columns of tables which have an internal, |
| unnamed SEQUENCE object.</p> |
| <p>SEQUENCE objects and IDENTITY columns are supported fully |
| according to the latest SQL 2008 Standard syntax.</p> |
| <p> |
| <span class="bold"><strong>Sequences</strong></span> |
| </p> |
| <p>The SQL:2008 syntax and usage is different from what is supported |
| by many existing database engines. Sequences are created with the |
| <code class="literal">CREATE SEQUENCE</code> command and their current value can |
| be modified at any time with <code class="literal">ALTER SEQUENCE</code>. The next |
| value for a sequence is retrieved with the <code class="literal">NEXT VALUE FOR |
| <name></code> expression. This expression can be used for |
| inserting and updating table rows.</p> |
| <div class="example"> |
| <a name="N10B67"></a> |
| <p class="title"> |
| <b>Example 4.1. inserting the next sequence value into a table row</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="programlisting">INSERT INTO mytable VALUES 2, 'John', NEXT VALUE FOR mysequence;</pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| <p>You can also use it in select statements. For example, if you want |
| to number the returned rows of a SELECT in sequential order, you can |
| use:</p> |
| <div class="example"> |
| <a name="N10B6E"></a> |
| <p class="title"> |
| <b>Example 4.2. numbering returned rows of a SELECT in sequential order</b> |
| </p> |
| <div class="example-contents"> |
| <pre class="programlisting">SELECT NEXT VALUE FOR mysequence, col1, col2 FROM mytable WHERE ...</pre> |
| </div> |
| </div> |
| <br class="example-break"> |
| <p>In version 2.0, the semantics of sequences is exactly as defined |
| by SQL:2008. If you use the same sequence twice in the same row in an |
| INSERT statement, you will get the same value as required by the |
| Standard.</p> |
| <p>The correct way to use a sequence value is the NEXT VALUE FOR |
| expression. You can query the SEQUENCES table for the next value that |
| will be returned from any of the defined sequences. The SEQUENCE_NAME |
| column contains the name and the NEXT_VALUE column contains the next |
| value to be returned. Note that this is only for getting information and |
| you should not use the sequence value.</p> |
| <p> |
| <span class="bold"><strong>Identity Auto-Increment |
| Columns</strong></span> |
| </p> |
| <p>Each table can contain a single auto-increment column, known as |
| the IDENTITY column. An IDENTITY column is a SMALLINT, INTEGER, BIGINT, |
| DECIMAL or NUMERIC column with its value generated by a sequence |
| generator.</p> |
| <p>In HyperSQL 2.0, an IDENTITY column is not by default treated as |
| the primary key for the table (as a result, multi-column primary keys |
| are possible with an IDENTITY column present).</p> |
| <p>The SQL standard syntax is used, which allows the initial value |
| and other options to be specified.<pre class="programlisting"><colname> [ INTEGER | BIGINT | DECIMAL | NUMERIC ] GENERATED { BY DEFAULT | ALWAYS} AS IDENTITY [( <options> )] [PRIMARY KEY]</pre> |
| </p> |
| <p>When you add a new row to such a table using an <code class="literal">INSERT |
| INTO <tablename> ... </code>statement, you can use the DEFAULT |
| keyword for the IDENTITY column, which results in an auto-generated |
| value for the column. The <code class="literal">IDENTITY() </code>function returns |
| the last value inserted into any IDENTITY column by this session. Each |
| session manages this function call separately and is not affected by |
| inserts in other sessions. Use <code class="literal">CALL IDENTITY() </code>as an |
| SQL statement to retrieve this value. If you want to use the value for a |
| field in a child table, you can use <code class="literal">INSERT INTO |
| <childtable> VALUES (...,IDENTITY(),...);</code>. Both types of |
| call to<code class="literal"> IDENTITY()</code> must be made before any additional |
| update or insert statements are issued by the session.</p> |
| <p>The last inserted IDENTITY value can also be retrieved via JDBC, |
| by specifying the Statement or PreparedStatement object to return the |
| generated value.</p> |
| <p>The next IDENTITY value to be used can be changed with following |
| statement. Note that this statement is not used in normal operation and |
| is only for special purposes: <pre class="programlisting">ALTER TABLE ALTER COLUMN <column name> RESTART WITH <new value>;</pre>For |
| backward compatibility, support has been retained for <code class="literal">CREATE |
| TABLE <tablename>(<colname> IDENTITY, ...)</code> as a |
| shortcut which defines the column both as an IDENTITY column and a |
| PRIMARY KEY column. Also, for backward compatibility, it is possible to |
| use NULL as the value of an IDENTITY column in an INSERT statement and |
| the value will be generated automatically. You should avoid these |
| compatibility features as they may be removed from future versions of |
| HyperSQL.</p> |
| <p>In the following example, the identity value for the first INSERT |
| statement is generated automatically using the DEFAULT keyword. The |
| second INSERT statement uses a call to the IDENTITY() function to |
| populate a row in the child table with the generated identity |
| value.</p> |
| <div class="informalexample"> |
| <pre class="programlisting">CREATE TABLE star (id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, |
| firstname VARCHAR(20), |
| lastname VARCHAR(20)) |
| CREATE TABLE movies (starid INTEGER, movieid INTEGER PRIMARY KEY, title VARCHAR(40)) |
| INSERT INTO star (id, firstname, lastname) VALUES (DEFAULT, 'Felix', 'the Cat') |
| INSERT INTO movies (starid, movieid, title) VALUES (IDENTITY(), 10, 'Felix in Hollywood') |
| </pre> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10BA3"></a>Tables</h3> |
| </div> |
| </div> |
| </div> |
| <p>In the SQL environment, tables are the most essential |
| components, as they hold all persistent data.</p> |
| <p>If TABLE is considered as metadata (i.e. without its actual |
| data) it is called a <span class="emphasis"><em>relation</em></span> in relational theory. |
| It has one or more columns, with each column having a distinct name and |
| a data type. A table usually has one or more constraints which limit the |
| values that can potentially be stored in the TABLE. These constraints |
| are discussed in the next section.</p> |
| <p>A single column of the table can be defined as IDENTITY. The |
| values stored in this column are auto-generated and are based on an |
| (unnamed) identity sequence.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10BAF"></a>Views</h3> |
| </div> |
| </div> |
| </div> |
| <p>A VIEW is similar to a TABLE but it does not permanently |
| contain rows of data. A view is defined as a QUERY EXPRESSION, which is |
| often a SELECT statement that references views and tables, but it can |
| also consist of a TABLE CONSTRUCTOR that does not reference any tables |
| or views.</p> |
| <p>A view has many uses:</p> |
| <div class="itemizedlist"> |
| <ul type="disc"> |
| <li> |
| <p>Hide the structure and column names of tables. The view can |
| represent one or more tables or views as a separate table. This can |
| include aggregate data, such as sums and averages, from other |
| tables.</p> |
| </li> |
| <li> |
| <p>Allow access to specific rows in a table. For example, allow |
| access to records that were added since a given date, while hiding |
| older records.</p> |
| </li> |
| <li> |
| <p>Allow access to specific columns. For example allow access to |
| columns that contain non-confidential information. Note that this |
| can also be achieved with the GRANT SELECT statement, using |
| column-level privileges</p> |
| </li> |
| </ul> |
| </div> |
| <p>A VIEW that returns the columns of a single ordinary TABLE may |
| be <em class="glossterm">updatable</em>. Some |
| <em class="glossterm">updatable</em> views are |
| <em class="glossterm">insertable-into</em>. When rows of an updatable view |
| are updated, or new rows are inserted, these changes are reflected in |
| the base table. A VIEW definition may specify that the inserted or |
| updated rows conform to the search condition of the view. This is done |
| with the CHECK OPTION clause.</p> |
| <p>Views share a name-space with tables.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10BCD"></a>Constraints</h3> |
| </div> |
| </div> |
| </div> |
| <p>A CONSTRAINT is a child schema object and can belong to a |
| DOMAIN or a TABLE. CONSTRAINT objects can be defined without specifying |
| a name. In this case the system generates a name for the new object |
| beginning with "SYS_".</p> |
| <p>In a DOMAIN, CHECK constraints can be defined that limits the |
| value represented by the DOMAIN. These constraints work exactly like a |
| CHECK constraint on a single column of a table as described |
| below.</p> |
| <p>In a TABLE, a constraint takes three basic forms.</p> |
| <p> |
| <span class="bold"><strong>CHECK</strong></span> |
| </p> |
| <p>A CHECK constraint consists of a <code class="literal"><search |
| condition></code> that must not be false (can be unknown) for each |
| row of the table. The <code class="literal"><search condition></code> can |
| reference all the columns of the current row, and if it contains a |
| <code class="literal"><subquery></code>, other tables and views in the |
| database (excluding its own table).</p> |
| <p> |
| <span class="bold"><strong>NOT NULL</strong></span> |
| </p> |
| <p>A simple form of check constraint is the NOT NULL constraint, |
| which applies to a single column.</p> |
| <p> |
| <span class="bold"><strong>UNIQUE</strong></span> |
| </p> |
| <p>A UNIQUE constraint is based on an equality comparison of |
| values of specific columns (taken together) of one row with the same |
| values from each of the other rows. The result of the comparison must |
| never be true (can be false or unknown). If a row of the table has NULL |
| in any of the columns of the constraint, it conforms to the constraint. |
| A unique constraint on multiple columns (c1, c2, c3, ..) means that in |
| no two rows, the sets of values for the columns can be equal unless at |
| lease one of them is NULL. Each single column taken by itself can have |
| repeat values in different rows. The following example satisfies a |
| UNIQUE constraint on the two columns</p> |
| <div class="example"> |
| <a name="N10BF2"></a> |
| <p class="title"> |
| <b>Example 4.3. Column values which satisfy a 2-column UNIQUE |
| constraint</b> |
| </p> |
| <div class="example-contents"> |
| <table summary="Simple list" border="0" class="simplelist"> |
| <tr> |
| <td>1,</td><td>2</td> |
| </tr> |
| <tr> |
| <td>2,</td><td>1</td> |
| </tr> |
| <tr> |
| <td>2,</td><td>2</td> |
| </tr> |
| <tr> |
| <td>NULL,</td><td>1</td> |
| </tr> |
| <tr> |
| <td>NULL,</td><td>1</td> |
| </tr> |
| <tr> |
| <td>1,</td><td>NULL</td> |
| </tr> |
| <tr> |
| <td>NULL,</td><td>NULL</td> |
| </tr> |
| <tr> |
| <td>NULL,</td><td>NULL</td> |
| </tr> |
| </table> |
| </div> |
| </div> |
| <br class="example-break"> |
| <p> |
| <span class="bold"><strong>PRIMARY KEY</strong></span> |
| </p> |
| <p>A PRIMARY KEY constraint is equivalent to a UNIQUE constraint |
| on one or more NOT NULL columns. Only one PRIMARY KEY can be defined in |
| each table.</p> |
| <p> |
| <span class="bold"><strong>FOREIGN KEY</strong></span> |
| </p> |
| <p>A FOREIGN key constraint is based on an equality comparison |
| between values of specific columns (taken together) of each row with the |
| values of the columns of a UNIQUE constraint on another table or the |
| same table. The result of the comparison must never be false (can be |
| unknown). A special form of FOREIGN KEY constraint, based on its CHECK |
| clause, allows the result to be unknown only if the values for all |
| columns are NULL. A FOREIGN key can be declared only if a UNIQUE |
| constraint exists on the referenced columns.</p> |
| <p>Constraints share a name space with assertions.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10C26"></a>Assertions</h3> |
| </div> |
| </div> |
| </div> |
| <p>An ASSERTION is a top-level schema objects. It consists of a |
| <code class="literal"><search condition></code> that must not be false (can |
| be unknown).</p> |
| <p>Assertions share a name-space with constraints</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10C30"></a>Triggers</h3> |
| </div> |
| </div> |
| </div> |
| <p>A TRIGGER is a child schema object that always belongs to a |
| TABLE or a VIEW.</p> |
| <p>Each time a DELETE, UPDATE or INSERT is performed on the table |
| or view, additional actions are taken by the triggers that have been |
| declared on the table or view.</p> |
| <p>Triggers are discussed in detail in chapter <a class="link" href="triggers-chapt.html" title="Chapter 9. Triggers">Triggers</a> |
| .</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10C3E"></a>Routines</h3> |
| </div> |
| </div> |
| </div> |
| <p>Routines are user-defined functions or procedures. The names |
| and usage of functions and procedures are different. FUNCTION is a |
| routine that can be referenced in many types of statements. PROCEDURE is |
| a routine that can be referenced only in a CALL statement.</p> |
| <p>There is a separate name-space for routines.</p> |
| <p>Because of the possibility of overloading, each routine can |
| have more than one name. The name of the routine is the same for all |
| overloaded variants, but each variant has a <em class="glossterm">specific |
| name</em>, different from all other routine names and specific |
| names in the schema. The <em class="glossterm">specific name</em> can be |
| specified in the routine definition statement. Otherwise it is assigned |
| by the engine. The specific name is used only for schema manipulation |
| statements, which need to reference a specific variant of the routine. |
| For example, if a routine has two signatures, each signature has its own |
| <em class="glossterm">specific name</em>. This allows the user to drop one |
| of the signatures while keeping the other.</p> |
| <p>Routines are discussed in detail in chapter <a class="link" href="sqlroutines-chapt.html" title="Chapter 8. SQL-Invoked Routines">SQL-Invoked Routines</a> .</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10C57"></a>Indexes</h3> |
| </div> |
| </div> |
| </div> |
| <p>Indexes are an implementation-defined extension to the SQL |
| Standard. HyperSQL has a dedicated name-space for indexes in each |
| schema.</p> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N10C5C"></a>Statements for Schema Definition and Manipulation</h2> |
| </div> |
| </div> |
| </div> |
| <p>Schemas and schema objects can be created, modified and dropped. |
| The SQL Standard defines a range of statements for this purpose. HyperSQL |
| supports many additional statements, especially for changing the |
| properties of existing schema objects.</p> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10C61"></a>Common Elements and Statements</h3> |
| </div> |
| </div> |
| </div> |
| <p>These elements and statements are used for different types of |
| object. They are described here, before the statements that can use |
| them.</p> |
| <a name="N10C66" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>identifier</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>definition of identifier</em></span> |
| </p> |
| <p> |
| <code class="literal"><identifier> ::= <regular identifier> | |
| <delimited identifier> | <SQL language identifier> |
| </code> |
| </p> |
| <p> |
| <code class="literal"><delimited identifier> ::= <double quote> |
| <character sequence> <double quote></code> |
| </p> |
| <p> |
| <code class="literal"><regular identifier> ::= <special character |
| sequence></code> |
| </p> |
| <p> |
| <code class="literal"><SQL language identifier> ::= <special |
| character sequence></code> |
| </p> |
| <p>A <code class="literal"><delimited identifier></code> is a sequence |
| of characters enclosed with double-quote symbols. All characters are |
| allowed in the character sequence.</p> |
| <p>A <code class="literal"><regular identifier></code> is a special |
| sequence of characters. It consists of letters, digits and the |
| underscore characters. It must begin with a letter.</p> |
| <p>A <code class="literal"><SQL language identifier></code> is similar |
| to <code class="literal"><regular identifier></code> but the letters can |
| range only from A-Z in the ASCII character set. This type of identifier |
| is used for names of CHARACTER SET objects.</p> |
| <p>If the character sequence of a delimited identifier is the same |
| as an undelimited identifier, it represents the same identifier. For |
| example "JOHN" is the same identifier as JOHN. In a <code class="literal"><regular |
| identifier></code> the case-normal form is considered for |
| comparison. This form consists of the upper-case of equivalent of all |
| the letters.</p> |
| <p>The character sequence length of all identifiers must be |
| between 1 and 128 characters.</p> |
| <p>A reserved word is one that is used by the SQL Standard for |
| special purposes. It is similar to a <code class="literal"><regular |
| identifier></code> but it cannot be used as an identifier for user |
| objects. If a reserved word is enclosed in double quote characters, it |
| becomes a quoted identifier and can be used for database |
| objects.</p> |
| <a name="N10C9C" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CASCADE or RESTRICT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop behavior</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop behavior> ::= CASCADE | |
| RESTRICT</code> |
| </p> |
| <p>The <code class="literal"><drop behavior></code> is a required |
| element of statements that drop a SCHEMA or a schema object. If |
| <code class="literal"><drop behavior></code> is not specified then |
| <code class="literal">RESTRICT</code> is implicit. It determines the effect of the |
| statement if there are other objects in the catalog that reference the |
| SCHEMA or the schema object. If RESTRICT is specified, the statement |
| fails if there are referencing objects. If CASCADE is specified, all the |
| referencing objects are modified or dropped with cascading effect. |
| Whether a referencing object is modified or dropped, depends on the kind |
| of schema object that is dropped.</p> |
| <a name="N10CB6" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>IF EXISTS</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop condition (HyperSQL)</em></span> |
| </p> |
| <p> |
| <code class="literal"><if exists clause> ::= IF |
| EXISTS</code> |
| </p> |
| <p>This clause is not part of the SQL standard and is a HyperSQL |
| extension to some commands that drop objects (schemas, tables, views, |
| sequences and indexes). If it is specified, then the statement does not |
| return an error if the drop statement is issued on a non-existent |
| object.</p> |
| <a name="N10CC7" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SPECIFIC</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>specific routine designator</em></span> |
| </p> |
| <p> |
| <code class="literal"><specific routine designator> ::= SPECIFIC |
| <routine type> <specific name> </code> |
| </p> |
| <p> |
| <code class="literal"><routine type> ::= ROUTINE | FUNCTION | |
| PROCEDURE</code> |
| </p> |
| <p>This clause is used in statements that need to specify one of |
| the multiple versions of an overloaded routine. The |
| <code class="literal"><specific name></code> is the one specified in the |
| <code class="literal"><routine definition></code> statement.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10CE1"></a>Renaming Objects</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N10CE4" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>RENAME</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>rename statement (HyperSQL)</em></span> |
| </p> |
| <p> |
| <code class="literal"><rename statement> ::= ALTER <object type> |
| <name> RENAME TO <new name></code> |
| </p> |
| <p> |
| <code class="literal"><object type> ::= CATALOG | SCHEMA | DOMAIN | |
| TYPE | TABLE | CONSTRAINT | INDEX | ROUTINE | SPECIFIC |
| ROUTINE</code> |
| </p> |
| <p> |
| <code class="literal"><column rename statement> ::= ALTER TABLE |
| <table name> ALTER COLUMN <name> RENAME TO <new |
| name></code> |
| </p> |
| <p>This statement is used to rename an existing object. It is not |
| part of the SQL Standard. The specified <code class="literal"><name></code> |
| is the existing name, which can be qualified with a schema name, while |
| the <code class="literal"><new name></code> is the new name for the |
| object.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10D01"></a>Commenting Objects</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N10D04" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>COMMENT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>comment statement (HyperSQL)</em></span> |
| </p> |
| <p> |
| <code class="literal"><comment statement> ::= COMMENT ON { TABLE | |
| COLUMN | ROUTINE } <name> IS <character string |
| literal></code> |
| </p> |
| <p>Adds a comment to the object metadata, which can later be read |
| from an INFORMATION_SCHEMA view. This command is not part of the SQL |
| Standard. The strange syntax is due to compatibility with other database |
| engines that support the statement.<code class="literal"> The <name></code> |
| is the name of a table, view, column or routine. The name of the column |
| consists of dot-separated<code class="literal"> <table name> . <column |
| name></code>. The name of the table, view or routine can be a |
| simple name. All names can be qualified with a schema name. If there is |
| alread a comment on the object, the new comment will replace |
| it.</p> |
| <p>The comments appear in the results returned by JDBC |
| DatabaseMetaData methods, getTables() and getColumns(). The |
| INFORMATION_SCHEMA.SYSTEM_COMMENTS view contains the comments. You can |
| query this view using the schema, table, and column names to retreive |
| the comments.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10D1D"></a>Schema Creation</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N10D20" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CREATE SCHEMA</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>schema definition</em></span> |
| </p> |
| <p>The CREATE_SCHEMA or DBA role is required in order to create a |
| schema. A schema can be created with or without schema objects. Schema |
| objects can always be added after creating the schema, or existing ones |
| can be dropped. Within the <code class="literal"><schema definition></code> |
| statement, all schema object creation takes place inside the newly |
| created schema. Therefore, if a schema name is specified for the schema |
| objects, the name must match that of the new schema. In addition to |
| statements for creating schema objects, the statement can include |
| instances of <code class="literal"><grant statement></code> and |
| <code class="literal"><role definition></code>. This is a curious aspect of |
| the SQL standard, as these elements do not really belong to schema |
| creation.</p> |
| <p> |
| <code class="literal"><schema definition> ::= CREATE SCHEMA <schema |
| name clause> [ <schema character set specification> ] [ |
| <schema element>... ]</code> |
| </p> |
| <p> |
| <code class="literal"><schema name clause> ::= <schema name> | |
| AUTHORIZATION <authorization identifier> | <schema name> |
| AUTHORIZATION <authorization identifier></code> |
| </p> |
| <p>If the name of the schema is specified simply as |
| <code class="literal"><schema name></code>, then the AUTHORIZATION is the |
| current user. Otherwise, the specified <code class="literal"><authorization |
| identifier></code> is used as the AUTHORIZATION for the schema. If |
| <code class="literal"><schema name></code> is omitted, then the name of the |
| schema is the same as the specified <code class="literal"><authorization |
| identifier></code>.</p> |
| <p> |
| <code class="literal"><schema element> ::= <table definition> | |
| <view definition> | <domain definition> | <character set |
| definition> | <collation definition> | <transliteration |
| definition> | <assertion definition> | <trigger |
| definition> | <user-defined type definition> | <user-defined |
| cast definition> | <user-defined ordering definition> | |
| <transform definition> | <schema routine> | <sequence |
| generator definition> | <grant statement> | <role |
| definition></code> |
| </p> |
| <p>An example of the command is given below. Note that a single |
| semicolon appears at the end, there should be no semicolon between the |
| statements:</p> |
| <pre class="programlisting"> CREATE SCHEMA ACCOUNTS AUTHORIZATION DBA |
| CREATE TABLE AB(A INTEGER, ...) |
| CREATE TABLE CD(C CHAR(10), ...) |
| CREATE VIEW VI AS SELECT ... |
| GRANT SELECT ON AB TO PUBLIC |
| GRANT SELECT ON CD TO JOE; |
| </pre> |
| <p>It is not really necessary to create a schema and all its |
| objects as one command. The schema can be created first, and its objects |
| can be created one by one.</p> |
| <a name="N10D54" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP SCHEMA</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop schema statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop schema statement> ::= DROP SCHEMA [ IF |
| EXISTS ] <schema name> [ IF EXISTS ] <drop behavior> |
| </code> |
| </p> |
| <p>This command destroys an existing schema. If <code class="literal"><drop |
| behavior></code> is <code class="literal">RESTRICT</code>, the schema must |
| be empty, otherwise an error is raised. If <code class="literal">CASCADE</code> is |
| specified, then all the objects contained in the schema are destroyed |
| with a CASCADE option.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N10D6E"></a>Table Creation and Manipulation</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N10D71" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CREATE TABLE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>table definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><table definition> ::= CREATE [ { <table |
| scope> | <table type> } ] TABLE <table name> <table |
| contents source> [ ON COMMIT { PRESERVE | DELETE } ROWS |
| ]</code> |
| </p> |
| <p> |
| <code class="literal"><table scope> ::= { GLOBAL | LOCAL } |
| TEMPORARY</code> |
| </p> |
| <p> |
| <code class="literal"><table type> :: = MEMORY | |
| CACHED</code> |
| </p> |
| <p> |
| <code class="literal"><table contents source> ::= <table element |
| list> | <as subquery clause></code> |
| </p> |
| <p> |
| <code class="literal"><table element list> ::= <left paren> |
| <table element> [ { <comma> <table element> }... ] |
| <right paren></code> |
| </p> |
| <p> |
| <code class="literal"><table element> ::= <column definition> | |
| <table constraint definition> | <like |
| clause></code> |
| </p> |
| <p> |
| <span class="emphasis"><em>like clause</em></span> |
| </p> |
| <p>A <code class="literal"><like clause></code> copies all column |
| definitions from another table into the newly created table. Its three |
| options indicate if the <code class="literal"><default clause></code>, |
| <code class="literal"><identity column specification></code> and |
| <code class="literal"><generation clause></code> associated with the column |
| definitions are copied or not. If an option is not specified, it |
| defaults to <code class="literal">EXCLUDING</code>. The <code class="literal"><generation |
| clause></code> refers to columns that are generated by an |
| expression but not to identity columns. All NOT NULL constraints are |
| copied with the original columns, other constraints are not. The |
| <code class="literal"><like clause></code> can be used multiple times, |
| allowing the new table to have copies of the column definitions of one |
| or more other tables.</p> |
| <div class="informalexample"> |
| <pre class="programlisting">CREATE TABLE t (id INTEGER PRIMARY KEY, LIKE atable INCLUDING DEFAULTS EXCLUDING IDENTITY) |
| </pre> |
| </div> |
| <p> |
| <code class="literal"><like clause> ::= LIKE <table name> [ |
| <like options> ]</code> |
| </p> |
| <p> |
| <code class="literal"><like options> ::= <like |
| option>...</code> |
| </p> |
| <p> |
| <code class="literal"><like option> ::= <identity option> | |
| <column default option> | <generation |
| option></code> |
| </p> |
| <p> |
| <code class="literal"><identity option> ::= INCLUDING IDENTITY | |
| EXCLUDING IDENTITY</code> |
| </p> |
| <p> |
| <code class="literal"><column default option> ::= INCLUDING DEFAULTS | |
| EXCLUDING DEFAULTS</code> |
| </p> |
| <p> |
| <code class="literal"><generation option> ::= INCLUDING GENERATED | |
| EXCLUDING GENERATED</code> |
| </p> |
| <p> |
| <span class="emphasis"><em>as subquery clause</em></span> |
| </p> |
| <p> |
| <code class="literal"><as subquery clause> ::= [ <left paren> |
| <column name list> <right paren> ] AS <table subquery> |
| { WITH NO DATA | WITH DATA }</code> |
| </p> |
| <p>An <code class="literal"><as subquery clause></code> used in table |
| definition creates a table based on a <code class="literal"><table |
| subquery></code>. This kind of table definition is similar to a |
| view definition. If <code class="literal">WITH DATA</code> is specified, then the |
| new table will contain the rows of data returned by the |
| <code class="literal"><table subquery></code>.</p> |
| <div class="informalexample"> |
| <pre class="programlisting">CREATE TABLE t (a, b, c) AS (SELECT * FROM atable) WITH DATA |
| </pre> |
| </div> |
| <a name="N10DD5" class="indexterm"></a> |
| <p> |
| <span class="emphasis"><em>column definition</em></span> |
| </p> |
| <p>A column definition consists of a <code class="literal"><column |
| name></code> and in most cases a <code class="literal"><data |
| type></code> or <code class="literal"><domain name></code> as minimum. |
| The other elements of <code class="literal"><column definition></code> are |
| optional. Each <code class="literal"><column name></code> in a table is |
| unique.</p> |
| <p> |
| <code class="literal"><column definition> ::= <column name> [ |
| <data type or domain name> ] [ <default clause> | |
| <identity column specification> | <generation clause> ] [ |
| <column constraint definition>... ] [ <collate clause> |
| ]</code> |
| </p> |
| <p> |
| <code class="literal"><data type or domain name> ::= <data type> |
| | <domain name></code> |
| </p> |
| <p> |
| <code class="literal"><column constraint definition> ::= [ |
| <constraint name definition> ] <column constraint> [ |
| <constraint characteristics> ]</code> |
| </p> |
| <p> |
| <code class="literal"><column constraint> ::= NOT NULL | <unique |
| specification> | <references specification> | <check |
| constraint definition></code> |
| </p> |
| <p> |
| <code class="literal"><identity column specification> ::= GENERATED { |
| ALWAYS | BY DEFAULT } AS IDENTITY [ <left paren> <common |
| sequence generator options> <right paren> ]</code> |
| </p> |
| <p> |
| <code class="literal"><generation clause> ::= GENERATED ALWAYS AS |
| <generation expression></code> |
| </p> |
| <p> |
| <code class="literal"><generation expression> ::= <left paren> |
| <value expression> <right paren></code> |
| </p> |
| <p>The <code class="literal"><identity column specification></code> |
| can be specified for only a single column of the table.</p> |
| <p>A <code class="literal"><column constraint definition></code> is a |
| shortcut for a <code class="literal"><table constraint definition></code>. A |
| constraint that is defined in this way is automatically turned into a |
| table constraint. A name is automatically generated for the constraint |
| and assigned to it.</p> |
| <p>The <code class="literal"><identity column specification></code> is |
| used for special columns which represent values based on unnamed |
| sequence generators. It is possible to insert a row into the able |
| without specifying a value for the column. The value is then generated |
| by the sequence generators according to its rules. An identity column |
| may or may not be the primary key. Example below:</p> |
| <div class="informalexample"> |
| <pre class="programlisting">CREATE TABLE t (id INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 100), name VARCHAR(20) PRIMARY KEY, ) |
| </pre> |
| </div> |
| <p>The <code class="literal"><generation clause></code> is used for |
| special columns which represent values based on the values held in other |
| columns in the same row. The <code class="literal"><value expression></code> |
| must reference only other, non-generated, columns of the table in the |
| same row. Therefore, any function used in the expression may not access |
| SQL-data, and no <code class="literal"><query expression></code> is allowed. |
| When <code class="literal"><generation clause></code> is used, |
| <code class="literal"><data type></code> or <code class="literal"><domain |
| name></code> may be omitted.</p> |
| <p>A generated column can be part of a foreign key or unique |
| constraints or a column of an index. This capability is the main reason |
| for using generated columns. A generated column may contain a formula |
| that computes a value based on the values of other columns. Fast |
| searches of the computed value can be performed when an index is |
| declared on the generated column. Or the computed values can be declared |
| to be unique, using a UNIQUE constraint on the table.</p> |
| <p>When a row is inserted into a table, or an existing row is |
| updated, no value except DEFAULT can be specified for a generated |
| column. In the example below, data is inserted into the non-generated |
| columns and the generated column will contain 'Felix the Cat' or 'Pink |
| Panther'.</p> |
| <div class="informalexample"> |
| <pre class="programlisting">CREATE TABLE t (id INTEGER PRIMARY KEY, |
| firstname VARCHAR(20), |
| lastname VARCHAR(20), |
| fullname VARCHAR(40) GENERATED ALWAYS AS (firstname || ' ' || lastname)) |
| INSERT INTO t (id, firstname, lastname) VALUES (1, 'Felix', 'the Cat') |
| INSERT INTO t (id, firstname, lastname, fullname) VALUES (2, 'Pink', 'Panther', DEFAULT) |
| </pre> |
| </div> |
| <a name="N10E33" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DEFAULT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>default clause</em></span> |
| </p> |
| <p>A default clause can be used if GENERATED is not specified. If |
| a column has a <code class="literal"><default clause></code> then it is |
| possible to insert a row into the table without specifying a value for |
| the column.</p> |
| <p> |
| <code class="literal"><default clause> ::= DEFAULT <default |
| option></code> |
| </p> |
| <p> |
| <code class="literal"><default option> ::= <literal> | |
| <datetime value function> | USER | CURRENT_USER | CURRENT_ROLE | |
| SESSION_USER | SYSTEM_USER | CURRENT_CATALOG | CURRENT_SCHEMA | |
| CURRENT_PATH | NULL</code> |
| </p> |
| <p>The type of the <code class="literal"><default option></code> must |
| match the type of the column.</p> |
| <a name="N10E4F" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CONSTRAINT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>constraint name and |
| characteristics</em></span> |
| </p> |
| <p> |
| <code class="literal"><constraint name definition> ::= CONSTRAINT |
| <constraint name></code> |
| </p> |
| <p> |
| <code class="literal"><constraint characteristics> ::= <constraint |
| check time> [ [ NOT ] DEFERRABLE [ <constraint check time> ] |
| ]</code> |
| </p> |
| <p> |
| <code class="literal"><constraint check time> ::= INITIALLY DEFERRED | |
| INITIALLY IMMEDIATE</code> |
| </p> |
| <p>Specify the name of a constraint and its characteristics. By |
| default the constraint is <code class="literal">NOT DEFERRABLE</code> and |
| <code class="literal">INITIALLY IMMEDIATE</code>. This means the constraint is |
| enforced as soon as a data change statement is executed. If |
| <code class="literal">INITIALLY DEFERRED</code> is specified, then the constraint |
| is enforced when the session commits. The characteristics must be |
| compatible. The constraint check time can be changed temporarily for an |
| SQL session. HyperSQL does not support deferring constraint enforcement. |
| This feature of the SQL Standard has been criticised because it allows a |
| session to read uncommitted data that violates database integrity |
| constraints but has not yet been checked.</p> |
| <a name="N10E6F" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CONSTRAINT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>table constraint definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><table constraint definition> ::= [ |
| <constraint name definition> ] <table constraint> [ |
| <constraint characteristics> ]</code> |
| </p> |
| <p> |
| <code class="literal"><table constraint> ::= <unique constraint |
| definition> | <referential constraint definition> | <check |
| constraint definition></code> |
| </p> |
| <p>Three kinds of constraint can be defined on a table: UNIQUE |
| (including PRIMARY KEY), FOREIGN KEY and CHECK. Each kind has its own |
| rules to limit the values that can be specified for different columns in |
| each row of the table.</p> |
| <a name="N10E83" class="indexterm"></a><a name="N10E88" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>UNIQUE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>unique constraint definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><unique constraint definition> ::= <unique |
| specification> <left paren> <unique column list> |
| <right paren> | UNIQUE ( VALUE )</code> |
| </p> |
| <p> |
| <code class="literal"><unique specification> ::= UNIQUE | PRIMARY |
| KEY</code> |
| </p> |
| <p> |
| <code class="literal"><unique column list> ::= <column name |
| list></code> |
| </p> |
| <p>A unique constraint is specified on a single column or on |
| multiple columns. On each set of columns taken together, only one UNIQUE |
| constraint can be specified. Each column of a PRIMARY KEY constraint has |
| an implicit NOT NULL constraint.</p> |
| <p>If <code class="literal">UNIQUE( VALUE )</code> is specified, the |
| constraint created on all columns of the table.</p> |
| <a name="N10EA4" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>FOREIGN KEY</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>referential constraint |
| definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><referential constraint definition> ::= FOREIGN |
| KEY <left paren> <referencing columns> <right paren> |
| <references specification></code> |
| </p> |
| <p> |
| <code class="literal"><references specification> ::= REFERENCES |
| <referenced table and columns> [ MATCH <match type> ] [ |
| <referential triggered action> ]</code> |
| </p> |
| <p> |
| <code class="literal"><match type> ::= FULL | PARTIAL | |
| SIMPLE</code> |
| </p> |
| <p> |
| <code class="literal"><referencing columns> ::= <reference column |
| list></code> |
| </p> |
| <p> |
| <code class="literal"><referenced table and columns> ::= <table |
| name> [ <left paren> <reference column list> <right |
| paren> ]</code> |
| </p> |
| <p> |
| <code class="literal"><reference column list> ::= <column name |
| list></code> |
| </p> |
| <p> |
| <code class="literal"><referential triggered action> ::= <update |
| rule> [ <delete rule> ] | <delete rule> [ <update |
| rule> ]</code> |
| </p> |
| <p> |
| <code class="literal"><update rule> ::= ON UPDATE <referential |
| action></code> |
| </p> |
| <p> |
| <code class="literal"><delete rule> ::= ON DELETE <referential |
| action></code> |
| </p> |
| <p> |
| <code class="literal"><referential action> ::= CASCADE | SET NULL | |
| SET DEFAULT | RESTRICT | NO ACTION</code> |
| </p> |
| <p>A referential constraint allows links to be established between |
| the rows of two tables. The specified list of <code class="literal"><referencing |
| columns></code> corresponds one by one to the columns of the |
| specified list of <code class="literal"><referenced columns></code> in |
| another table (or sometimes in the same table). For each row in the |
| table, a row must exist in the referenced table with equivalent values |
| in the two column lists. There must exist a single unique constraint in |
| the referenced table on all the <code class="literal"><referenced |
| columns></code>.</p> |
| <p>The <code class="literal">[ MATCH match type ]</code> clause is optional |
| and has an effect only on multi-column foreign keys and only on rows |
| containing at least a NULL in one of the <code class="literal"><referencing |
| columns></code>. If the clause is not specified, MATCH SIMPLE is |
| the default. If <code class="literal">MATCH SIMPLE</code> is specified, then any |
| NULL means the row can exist (without a corresponding row in the |
| referenced table). If <code class="literal">MATCH FULL</code> is specified then |
| either all the column values must be NULL or none of them. |
| <code class="literal">MATCH PARTIAL</code> allows any NULL but the non NULL values |
| must match those of a row in the referenced table. HyperSQL does not |
| support <code class="literal">MATCH PARTIAL</code>.</p> |
| <p>Referential actions are specified with ON UPDATE and ON DELETE |
| clauses. These actions take place when a row in the referenced table |
| (the parent table) has referencing rows in the referencing table and it |
| is deleted or modified with any SQL statement. The default is NO ACTION. |
| This means the SQL statement that causes the DELETE or UPDATE is |
| terminated with an exception. The RESTRICT option is similar and works |
| exactly the same without deferrable constraints (which are not allowed |
| by HyperSQL). The other three options, CASCADE, SET NULL and SET DEFAULT |
| all allow the DELETE or UPDATE statement to complete. With DELETE |
| statements the CASCADE option results in the referencing rows to be |
| deleted. With UPDATE statements, the changes to the values of the |
| referenced columns are copied to the referencing rows. With both DELETE |
| or UPDATE statement, the SET NULL option results in the columns of the |
| referencing rows to be set to NULL. Similarly, the SET DEFAULT option |
| results in the columns of the referencing rows to be set to their |
| default values.</p> |
| <a name="N10EEF" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CHECK</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>check constraint definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><check constraint definition> ::= CHECK <left |
| paren> <search condition> <right |
| paren></code> |
| </p> |
| <p>A CHECK constraint can exist for a TABLE or for a DOMAIN. The |
| <code class="literal"><search condition></code> evaluates to an SQL BOOLEAN |
| value for each row of the table. Within the <code class="literal"><search |
| condition></code> all columns of the table row can be referenced. |
| For all rows of the table, the <code class="literal"><search |
| condition></code> evaluates to TRUE or UNKNOWN. When a new row is |
| inserted, or an existing row is updated, the <code class="literal"><search |
| condition></code> is evaluated and if it is FALSE, the insert or |
| update fails.</p> |
| <p>A CHECK constraint for a DOMAIN is similar. In its |
| <code class="literal"><search condition></code>, the term VALUE is used to |
| represents the value to which the DOMAIN applies.</p> |
| <div class="informalexample"> |
| <pre class="programlisting">CREATE TABLE t (a VARCHAR(20) CHECK (a IS NOT NULL AND CHARACTER_LENGTH(a) > 2)) |
| </pre> |
| </div> |
| <p>The search condition of a CHECK constraint cannot contain any |
| function that is not deterministic. A check constraint is a data |
| integrity constraint, therefore it must hold with respect to the rest of |
| the data in the database. It cannot use values that are temporal or |
| ephemeral. For example CURRENT_USER is a function that returns different |
| values depending on who is using the database, or CURRENT_DATE changes |
| day-to-day. Some temporal expressions are retrospectively deterministic |
| and are allowed in check constraints. For example, (CHECK VALUE < |
| CURRENT_DATE) is valid, because CURRENT_DATE will not move backwards in |
| time, but (CHECK VALUE > CURRENT_DATE) is not acceptable.</p> |
| <p>If you want to enforce the condition that a date value that is |
| inserted into the database belongs to the future (at the time of |
| insertion), or any similar constraint, then use a TRIGGER with the |
| desired condition.</p> |
| <a name="N10F18" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SET TABLE |
| writeability</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>set table write property |
| (HyperSQL)</em></span> |
| </p> |
| <p> |
| <code class="literal"><set table read only statement> ::= SET TABLE |
| <table name> { READ ONLY | READ WRITE }</code> |
| </p> |
| <p>Set the writeability property of a table. Tables are writable |
| by default. This statement can be used to change the property between |
| <code class="literal">READ ONLY</code> and <code class="literal">READ WRITE</code>. This is |
| a feature of HyperSQL.</p> |
| <a name="N10F2F" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SET TABLE SOURCE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>set table source statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><set table source statement> ::= SET TABLE |
| <table name> SOURCE <file and options> |
| [DESC]</code> |
| </p> |
| <p> |
| <code class="literal"><file and options>::= <doublequote> |
| <file path> [<semicolon> <property>...] |
| <doublequote> </code> |
| </p> |
| <p>Set the text source for a text table. This statement cannot be |
| used for tables that are not defined as TEXT TABLE.</p> |
| <div class="variablelist"> |
| <p class="title"> |
| <b>Supported Properties</b> |
| </p> |
| <table border="0"> |
| <col valign="top" align="left"> |
| <tbody> |
| <tr> |
| <td> |
| <p> |
| <span class="term">quoted = { true | false }</span> |
| </p> |
| </td><td> |
| <p>default is true. If false, treats double quotes as normal |
| characters</p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">all_quoted = { true | false }</span> |
| </p> |
| </td><td> |
| <p>default is false. If true, adds double quotes around all |
| fields.</p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">encoding = <encoding name></span> |
| </p> |
| </td><td> |
| <p>character encoding for text and character fields, for |
| example, encoding=UTF-8</p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">ignore_first = { true | false }</span> |
| </p> |
| </td><td> |
| <p>default is false. If true ignores the first line of the |
| file</p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">cache_scale= <numeric value></span> |
| </p> |
| </td><td> |
| <p>exponent to calculate rows of the text file in cache. |
| Default is 8, equivalent to nearly 800 rows</p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">cache_size_scale = <numeric value>r</span> |
| </p> |
| </td><td> |
| <p>exponent to calculate average size of each row in cache. |
| Default is 8, equivalent to 256 bytes per row.</p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">fs = <unquoted character></span> |
| </p> |
| </td><td> |
| <p>field separator</p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">vs = <unquoted character></span> |
| </p> |
| </td><td> |
| <p>varchar separator</p> |
| </td> |
| </tr> |
| </tbody> |
| </table> |
| </div> |
| <div class="variablelist"> |
| <p class="title"> |
| <b>Special indicators for HyperSQL Text Table separators</b> |
| </p> |
| <table border="0"> |
| <col valign="top" align="left"> |
| <tbody> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\semi</span> |
| </p> |
| </td><td> |
| <p>semicolon</p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\quote</span> |
| </p> |
| </td><td> |
| <p>quote</p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\space</span> |
| </p> |
| </td><td> |
| <p>space character</p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\apos</span> |
| </p> |
| </td><td> |
| <p>apostrophe</p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\n</span> |
| </p> |
| </td><td> |
| <p>newline - Used as an end anchor (like $ in regular |
| expressions)</p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\r</span> |
| </p> |
| </td><td> |
| <p>carriage return</p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\t</span> |
| </p> |
| </td><td> |
| <p>tab</p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\\</span> |
| </p> |
| </td><td> |
| <p>backslash</p> |
| </td> |
| </tr> |
| <tr> |
| <td> |
| <p> |
| <span class="term">\u####</span> |
| </p> |
| </td><td> |
| <p>a Unicode character specified in hexadecimal</p> |
| </td> |
| </tr> |
| </tbody> |
| </table> |
| </div> |
| <p>In the example below, the text source of the table is set to |
| "myfile", the field separator to the pipe symbol, and the long varchar |
| separator to the tilde symbol.</p> |
| <pre class="programlisting"> SET TABLE mytable SOURCE 'myfile;fs=|;vs=.;lvs=~'</pre> |
| <p>Only a user with the DBA role can execute this |
| statement.</p> |
| <a name="N10FB5" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SET TABLE SOURCE |
| HEADER</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>set table source header |
| statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><set table source header statement> ::= SET |
| TABLE <table name> SOURCE HEADER <header |
| string></code> |
| </p> |
| <p>Set the header for the text source for a text table. If this |
| command is used, the <code class="literal"><header string></code> is used as |
| the first line of the source file of the text table. This line is not |
| part of the table data. Only a user with the DBA role can execute this |
| statement.</p> |
| <a name="N10FC9" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SET TABLE SOURCE |
| on-off</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>set table source on-off |
| statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><set table source on-off statement> ::= SET |
| TABLE <table name> SOURCE { ON | OFF } </code> |
| </p> |
| <p>Attach or detach a text table from its text source. This |
| command does not change the properties or the name of the file that is |
| the source of a text table. When OFF is specified, the command detaches |
| the table from its source and closes the file for the source. In this |
| state, it is not possible to read or write to the table. This allows the |
| user to replace the file with a different file, or delete it. When ON is |
| specified, the source file is read. Only a user with the DBA role can |
| execute this statement</p> |
| <a name="N10FDA" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ALTER TABLE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>alter table statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><alter table statement> ::= ALTER TABLE |
| <table name> <alter table action></code> |
| </p> |
| <p> |
| <code class="literal"><alter table action> ::= <add column |
| definition> | <alter column definition> | <drop column |
| definition> | <add table constraint definition> | <drop |
| table constraint definition></code> |
| </p> |
| <p>Change the definition of a table. Specific types of this |
| statement are covered below.</p> |
| <a name="N10FEE" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ADD COLUMN</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>add column definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><add column definition> ::= ADD [ COLUMN ] |
| <column definition> [ BEFORE <other column name> |
| ]</code> |
| </p> |
| <p>Add a column to an existing table. The <code class="literal"><column |
| definition></code> is specified the same way as it is used in |
| <code class="literal"><table definition></code>. HyperSQL allows the use of |
| <code class="literal">[ BEFORE <other column name> ]</code> to specify at |
| which position the new column is added to the table.</p> |
| <p>If the table contains rows, the new column must have a |
| <code class="literal"><default clause></code> or use one of the forms of |
| GENERATED. The column values for each row is then filled with the result |
| of the <code class="literal"><default clause></code> or the generated |
| value.</p> |
| <a name="N11010" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ALTER COLUMN</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>alter column definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><alter column definition> ::= ALTER [ COLUMN ] |
| <column name> <alter column action></code> |
| </p> |
| <p> |
| <code class="literal"><alter column action> ::= <set column default |
| clause> | <drop column default clause> | <alter column data |
| type clause> | <alter identity column specification> | |
| <alter column nullability> | <alter column |
| name></code> |
| </p> |
| <p>Change a column and its definition. Specific types of this |
| statement are covered below. See also the RENAME statement |
| above.</p> |
| <a name="N11024" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SET DEFAULT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>set column default clause</em></span> |
| </p> |
| <p> |
| <code class="literal"><set column default clause> ::= SET <default |
| clause></code> |
| </p> |
| <p>Set the default clause for a column. This can be used if the |
| column is not defined as GENERATED.</p> |
| <a name="N11035" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP DEFAULT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop column default clause</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop column default clause> ::= DROP |
| DEFAULT</code> |
| </p> |
| <p>Drop the default clause from a column.</p> |
| <a name="N11046" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SET DATA TYPE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>alter column data type clause</em></span> |
| </p> |
| <p> |
| <code class="literal"><alter column data type clause> ::= SET DATA |
| TYPE <data type></code> |
| </p> |
| <p>Change the declared type of a column. The (proposed) SQL |
| Standard allows only changes to type properties such as maximum length, |
| precision, or scale, and only changes that cause the property to |
| enlarge. HyperSQL allows changing the type if all the existing values |
| can be cast into the new type without string truncation or loss of |
| significant digits.</p> |
| <a name="N11057" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>alter identity |
| column</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>alter identity column |
| specification</em></span> |
| </p> |
| <p> |
| <code class="literal"><alter identity column specification> ::= |
| <alter identity column option>...</code> |
| </p> |
| <p> |
| <code class="literal"><alter identity column option> ::= <alter |
| sequence generator restart option> | SET <basic sequence generator |
| option></code> |
| </p> |
| <p>Change the properties of an identity column. This command is |
| similar to the commands used for changing the properties of named |
| SEQUENCE objects discussed in this section.</p> |
| <a name="N1106B" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SET NULL</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>alter column nullability</em></span> |
| </p> |
| <p> |
| <code class="literal"><alter column nullability> ::= SET |
| NULL</code> |
| </p> |
| <p>Removes a NOT NULL constraint from a column. This option is |
| specific to HyperSQL</p> |
| <a name="N1107C" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP COLUMN</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop column definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop column definition> ::= DROP [ COLUMN ] |
| <column name> <drop behavior></code> |
| </p> |
| <p>Destroy a column of a base table. The <code class="literal"><drop |
| behavior></code> is either <code class="literal">RESTRICT</code> or |
| <code class="literal">CASCADE</code>. If the column is referenced in a table |
| constraint that references other columns as well as this column, or if |
| the column is referenced in a VIEW, or the column is referenced in a |
| TRIGGER, then the statement will fail if <code class="literal">RESTRICT</code> is |
| specified. If <code class="literal">CASCADE</code> is specified, then any |
| CONSTRAINT, VIEW or TRIGGER object that references the column is dropped |
| with a cascading effect.</p> |
| <a name="N1109C" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ADD CONSTRAINT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>add table constraint definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><add table constraint definition> ::= ADD |
| <table constraint definition></code> |
| </p> |
| <p>Add a constraint to a table. The existing rows of the table |
| must conform to the added constraint, otherwise the statement will not |
| succeed.</p> |
| <a name="N110AD" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP CONSTRAINT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop table constraint definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop table constraint definition> ::= DROP |
| CONSTRAINT <constraint name> <drop |
| behavior></code> |
| </p> |
| <p>Destroy a constraint on a table. The <code class="literal"><drop |
| behavior></code> has an effect only on UNIQUE and PRIMARY KEY |
| constraints. If such a constraint is referenced by a FOREIGN KEY |
| constraint, the FOREIGN KEY constraint will be dropped if |
| <code class="literal">CASCADE</code> is specified. If the columns of such a |
| constraint are used in a GROUP BY clause in the query expression of a |
| VIEW or another kind of schema object, and a functional dependency |
| relationship exists between these columns and the other columns in that |
| query expression, then the VIEW or other schema object will be dropped |
| when <code class="literal">CASCADE</code> is specified.</p> |
| <a name="N110C7" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP TABLE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop table statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop table statement> ::= DROP TABLE [ IF |
| EXISTS ] <table name> [ IF EXISTS ] <drop |
| behavior></code> |
| </p> |
| <p>Destroy a table. The default drop behaviour is RESTRICT and |
| will cause the statement to fail if there is any view or foreign key |
| constraint that references the table. If <code class="literal"><drop |
| behavior></code> is <code class="literal">CASCADE</code>, it causes all |
| schema objects that reference the table to drop. Referencing views are |
| dropped. In the case of foreign key constraints that reference the |
| table, the constraint is dropped, rather than the TABLE or DOMAIN that |
| contains it.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N110DE"></a>View Creation and Manipulation</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N110E1" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CREATE VIEW</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>view definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><view definition> ::= CREATE [ RECURSIVE ] VIEW |
| <table name> <view specification> AS <query |
| expression> [ WITH [ CASCADED | LOCAL ] CHECK OPTION |
| ]</code> |
| </p> |
| <p> |
| <code class="literal"><view specification> ::= [ <left paren> |
| <view column list> <right paren> ]</code> |
| </p> |
| <p> |
| <code class="literal"><view column list> ::= <column name |
| list></code> |
| </p> |
| <p>Define a view. The <code class="literal"><query expression></code> |
| is a SELECT or similar statement. The <code class="literal"><view column |
| list></code> is the list of unique names for the columns of the |
| view. The number of columns in the <code class="literal"><view column |
| list></code> must match the number of columns returned by the |
| <code class="literal"><query expression></code>. If <code class="literal"><view column |
| list></code> is not specified, then the columns of the |
| <code class="literal"><query expression></code> should have unique names and |
| are used as the names of the view column.</p> |
| <p>Some views are updatable. As covered elsewhere, an updatable |
| view is based on a single table or updatable view. For updatable views, |
| the optional <code class="literal">CHECK OPTION</code> clause can be specified. If |
| this option is specified, then if a row of the view is updated or a new |
| row is inserted into the view, then it should contain such values that |
| the row would be included in the view after the change. If <code class="literal">WITH |
| CASCADED CHECK OPTION</code> is specified, then if the |
| <code class="literal"><query expression></code> of the view references |
| another view, then the search condition of the underlying view should |
| also be satisfied by the update or insert operation.</p> |
| <p>More on recursive...</p> |
| <a name="N11117" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP VIEW</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop view statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop view statement> ::= DROP VIEW [ IF EXISTS |
| ] <table name> [ IF EXISTS ] <drop |
| behavior></code> |
| </p> |
| <p>Destroy a view. The <code class="literal"><drop behavior></code> is |
| similar to dropping a table.</p> |
| <a name="N1112B" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ALTER VIEW</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>alter view statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><alter view statement> ::= ALTER VIEW <table |
| name> <view specification> AS <query expression> [ WITH [ |
| CASCADED | LOCAL ] CHECK OPTION ]</code> |
| </p> |
| <p>Alter a view. The statement is otherwise identical to CREATE |
| VIEW. The new definition replaces the old. If there are database objects |
| such as routines or views that reference the view, then these objects |
| are recompiled with the new view definition. If the new definition is |
| not compatible, the statement fails.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N1113C"></a>Domain Creation and Manipulation</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N1113F" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CREATE DOMAIN</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>domain definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><domain definition> ::= CREATE DOMAIN <domain |
| name> [ AS ] <predefined type> [ <default clause> ] [ |
| <domain constraint>... ] [ <collate clause> |
| ]</code> |
| </p> |
| <p> |
| <code class="literal"><domain constraint> ::= [ <constraint name |
| definition> ] <check constraint definition> [ <constraint |
| characteristics> ]</code> |
| </p> |
| <p>Define a domain. Although a DOMAIN is not strictly a type in |
| the SQL Standard, it can be informally considered as a type. A DOMAIN is |
| based on a <code class="literal"><predefined type></code>, which is a base |
| type defined by the Standard. It can have a <code class="literal"><default |
| clause></code>, similar to a column default clause. It can also |
| have one or more CHECK constraints which limit the values that can be |
| assigned to a column or variable that has the DOMAIN as its |
| type.</p> |
| <div class="informalexample"> |
| <pre class="programlisting">CREATE DOMAIN valid_string AS VARCHAR(20) DEFAULT 'NO VALUE' CHECK (value IS NOT NULL AND CHARACTER_LENGTH(value) > 2) |
| </pre> |
| </div> |
| <a name="N1115C" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ALTER DOMAIN</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>alter domain statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><alter domain statement> ::= ALTER DOMAIN |
| <domain name> <alter domain action></code> |
| </p> |
| <p> |
| <code class="literal"><alter domain action> ::= <set domain default |
| clause> | <drop domain default clause> | <add domain |
| constraint definition> | <drop domain constraint |
| definition></code> |
| </p> |
| <p>Change a domain and its definition.</p> |
| <a name="N11170" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SET DEFAULT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>set domain default clause</em></span> |
| </p> |
| <p> |
| <code class="literal"><set domain default clause> ::= SET <default |
| clause></code> |
| </p> |
| <p>Set the default value in a domain.</p> |
| <a name="N11181" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP DEFAULT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop domain default clause</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop domain default clause> ::= DROP |
| DEFAULT</code> |
| </p> |
| <p>Remove the default clause of a domain.</p> |
| <a name="N11192" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ADD CONSTRAINT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>add domain constraint definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><add domain constraint definition> ::= ADD |
| <domain constraint></code> |
| </p> |
| <p>Add a constraint to a domain.</p> |
| <a name="N111A3" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP CONSTRAINT</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop domain constraint |
| definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop domain constraint definition> ::= DROP |
| CONSTRAINT <constraint name></code> |
| </p> |
| <p>Destroy a constraint on a domain. If the <code class="literal"><drop |
| behavior></code> is <code class="literal">CASCADE</code>, and the constraint |
| is a UNIQUE constraint which is referenced by a FOREIGN KEY constraint |
| on another table, then the FOREIGN KEY constraint is also |
| dropped.</p> |
| <a name="N111BA" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP DOMAIN</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop domain statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop domain statement> ::= DROP DOMAIN |
| <domain name> <drop behavior></code> |
| </p> |
| <p>Destroy a domain. If <code class="literal"><drop behavior></code> |
| is <code class="literal">CASCADE</code>, it works differently from most other |
| objects. If a table features a column of the specified DOMAIN, the |
| column survives and inherits the DEFAULT CLAUSE, and the CHECK |
| CONSTRAINT of the DOMAIN.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N111D1"></a>Trigger Creation</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N111D4" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CREATE TRIGGER</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>trigger definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><trigger definition> ::= CREATE TRIGGER |
| <trigger name> <trigger action time> <trigger event> |
| ON <table name> [ REFERENCING <transition table or variable |
| list> ] <triggered action></code> |
| </p> |
| <p> |
| <code class="literal"><trigger action time> ::= BEFORE | AFTER | |
| INSTEAD OF</code> |
| </p> |
| <p> |
| <code class="literal"><trigger event> ::= INSERT | DELETE | UPDATE [ |
| OF <trigger column list> ]</code> |
| </p> |
| <p> |
| <code class="literal"><trigger column list> ::= <column name |
| list></code> |
| </p> |
| <p> |
| <code class="literal"><triggered action> ::= [ FOR EACH { ROW | |
| STATEMENT } ] [ <triggered when clause> ] <triggered SQL |
| statement></code> |
| </p> |
| <p> |
| <code class="literal"><triggered when clause> ::= WHEN <left |
| paren> <search condition> <right |
| paren></code> |
| </p> |
| <p> |
| <code class="literal"><triggered SQL statement> ::= <SQL procedure |
| statement> | BEGIN ATOMIC { <SQL procedure statement> |
| <semicolon> }... END | [QUEUE <integer literal>] [NOWAIT] |
| CALL <HSQLDB trigger class FQN></code> |
| </p> |
| <p> |
| <code class="literal"><transition table or variable list> ::= |
| <transition table or variable>...</code> |
| </p> |
| <p> |
| <code class="literal"><transition table or variable> ::= OLD [ ROW ] [ |
| AS ] <old transition variable name> | NEW [ ROW ] [ AS ] <new |
| transition variable name> | OLD TABLE [ AS ] <old transition table |
| name> | NEW TABLE [ AS ] <new transition table |
| name></code> |
| </p> |
| <p> |
| <code class="literal"><old transition table name> ::= <transition |
| table name></code> |
| </p> |
| <p> |
| <code class="literal"><new transition table name> ::= <transition |
| table name></code> |
| </p> |
| <p> |
| <code class="literal"><transition table name> ::= |
| <identifier></code> |
| </p> |
| <p> |
| <code class="literal"><old transition variable name> ::= |
| <correlation name></code> |
| </p> |
| <p> |
| <code class="literal"><new transition variable name> ::= |
| <correlation name></code> |
| </p> |
| <p>Trigger definition is a relatively complex statement. The |
| combination of <code class="literal"><trigger action time></code> and |
| <code class="literal"><trigger event></code> determines the type of the |
| trigger. Examples include BEFORE DELETE, AFTER UPDATE, INSTEAD OF |
| INSERT. If the optional <code class="literal">[ OF <trigger column list> |
| ]</code> is specified for an UPDATE trigger, then the trigger is |
| activated only if one of the columns that is in the <code class="literal"><trigger |
| column list></code> is specified in the UPDATE statement that |
| activates the trigger.</p> |
| <p>If a trigger is <code class="literal">FOR EACH ROW</code>, which is the |
| default option, then the trigger is activated for each row of the table |
| that is affected by the execution of an SQL statement. Otherwise, it is |
| activated once only per statement execution. In the first case, there is |
| a before and after state for each row. For UPDATE triggers, both before |
| and after states exist, representing the row before the update, and |
| after the update. For DELETE, triggers, there is only a before state. |
| For INSERT triggers, there is only an after state. If a trigger is |
| <code class="literal">FOR EACH STATEMENT</code>, then a transient table is created |
| containing all the rows for the before state and another transient table |
| is created for the after state.</p> |
| <p>The <code class="literal">[ REFERENCING <transition table or variable> |
| ]</code> is used to give a name to the before and after data row or |
| table. This name can be referenced in the <code class="literal"><SQL procedure |
| statement></code> to access the data.</p> |
| <p>The optional <code class="literal"><triggered when clause></code> |
| is a search condition, similar to the search condition of a DELETE or |
| UPDATE statement. If the search condition is not TRUE for a row, then |
| the trigger is not activated for that row.</p> |
| <p>The <code class="literal"><SQL procedure statement></code> is |
| limited to INSERT, DELETE, UPDATE and MERGE statements.</p> |
| <p>The <code class="literal"><HSQLDB trigger class FQN></code> is a |
| delimited identifier that contains the fully qualified name of a Java |
| class that implements the <code class="classname">org.hsqldb.Trigger</code> |
| interface.</p> |
| <p>Early releases of HyperSQL version 2.0 do not allow the use of |
| OLD TABLE or NEW TABLE in statement level trigger definitions.</p> |
| <a name="N1123C" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP TRIGGER</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop trigger statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop trigger statement> ::= DROP TRIGGER |
| <trigger name></code> |
| </p> |
| <p>Destroy a trigger.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N1124D"></a>Routine Creation</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N11250" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>schema routine</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>SQL-invoked routine</em></span> |
| </p> |
| <p> |
| <code class="literal"><SQL-invoked routine> ::= <schema |
| routine></code> |
| </p> |
| <p> |
| <code class="literal"><schema routine> ::= <schema procedure> | |
| <schema function></code> |
| </p> |
| <p> |
| <code class="literal"><schema procedure> ::= CREATE <SQL-invoked |
| procedure></code> |
| </p> |
| <p> |
| <code class="literal"><schema function> ::= CREATE <SQL-invoked |
| function></code> |
| </p> |
| <p> |
| <code class="literal"><SQL-invoked procedure> ::= PROCEDURE <schema |
| qualified routine name> <SQL parameter declaration list> |
| <routine characteristics> <routine body></code> |
| </p> |
| <p> |
| <code class="literal"><SQL-invoked function> ::= { <function |
| specification> | <method specification designator> } |
| <routine body></code> |
| </p> |
| <p> |
| <code class="literal"><SQL parameter declaration list> ::= <left |
| paren> [ <SQL parameter declaration> [ { <comma> <SQL |
| parameter declaration> }... ] ] <right |
| paren></code> |
| </p> |
| <p> |
| <code class="literal"><SQL parameter declaration> ::= [ <parameter |
| mode> ] [ <SQL parameter name> ] <parameter type> [ |
| RESULT ]</code> |
| </p> |
| <p> |
| <code class="literal"><parameter mode> ::= IN | OUT | |
| INOUT</code> |
| </p> |
| <p> |
| <code class="literal"><parameter type> ::= <data |
| type></code> |
| </p> |
| <p> |
| <code class="literal"><function specification> ::= FUNCTION <schema |
| qualified routine name> <SQL parameter declaration list> |
| <returns clause> <routine characteristics> [ <dispatch |
| clause> ]</code> |
| </p> |
| <p> |
| <code class="literal"><method specification designator> ::= SPECIFIC |
| METHOD <specific method name> | [ INSTANCE | STATIC | CONSTRUCTOR |
| ] METHOD <method name> <SQL parameter declaration list> [ |
| <returns clause> ] FOR <schema-resolved user-defined type |
| name></code> |
| </p> |
| <p> |
| <code class="literal"><routine characteristics> ::= [ <routine |
| characteristic>... ]</code> |
| </p> |
| <p> |
| <code class="literal"><routine characteristic> ::= <language |
| clause> | <parameter style clause> | SPECIFIC <specific |
| name> | <deterministic characteristic> | <SQL-data access |
| indication> | <null-call clause> | <returned result sets |
| characteristic> | <savepoint level |
| indication></code> |
| </p> |
| <p> |
| <code class="literal"><savepoint level indication> ::= NEW SAVEPOINT |
| LEVEL | OLD SAVEPOINT LEVEL</code> |
| </p> |
| <p> |
| <code class="literal"><returned result sets characteristic> ::= |
| DYNAMIC RESULT SETS <maximum returned result |
| sets></code> |
| </p> |
| <p> |
| <code class="literal"><parameter style clause> ::= PARAMETER STYLE |
| <parameter style></code> |
| </p> |
| <p> |
| <code class="literal"><dispatch clause> ::= STATIC |
| DISPATCH</code> |
| </p> |
| <p> |
| <code class="literal"><returns clause> ::= RETURNS <returns |
| type></code> |
| </p> |
| <p> |
| <code class="literal"><returns type> ::= <returns data type> [ |
| <result cast> ] | <returns table type></code> |
| </p> |
| <p> |
| <code class="literal"><returns table type> ::= TABLE <table |
| function column list></code> |
| </p> |
| <p> |
| <code class="literal"><table function column list> ::= <left |
| paren> <table function column list element> [ { <comma> |
| <table function column list element> }... ] <right |
| paren></code> |
| </p> |
| <p> |
| <code class="literal"><table function column list element> ::= |
| <column name> <data type></code> |
| </p> |
| <p> |
| <code class="literal"><result cast> ::= CAST FROM <result cast from |
| type></code> |
| </p> |
| <p> |
| <code class="literal"><result cast from type> ::= <data type> [ |
| <locator indication> ]</code> |
| </p> |
| <p> |
| <code class="literal"><returns data type> ::= <data type> [ |
| <locator indication> ]</code> |
| </p> |
| <p> |
| <code class="literal"><routine body> ::= <SQL routine spec> | |
| <external body reference></code> |
| </p> |
| <p> |
| <code class="literal"><SQL routine spec> ::= [ <rights clause> ] |
| <SQL routine body></code> |
| </p> |
| <p> |
| <code class="literal"><rights clause> ::= SQL SECURITY INVOKER | SQL |
| SECURITY DEFINER</code> |
| </p> |
| <p> |
| <code class="literal"><SQL routine body> ::= <SQL procedure |
| statement></code> |
| </p> |
| <p> |
| <code class="literal"><external body reference> ::= EXTERNAL [ NAME |
| <external routine name> ] [ <parameter style clause> |
| ]</code> |
| </p> |
| <p> |
| <code class="literal"><parameter style> ::= SQL | |
| GENERAL</code> |
| </p> |
| <p> |
| <code class="literal"><deterministic characteristic> ::= DETERMINISTIC |
| | NOT DETERMINISTIC</code> |
| </p> |
| <p> |
| <code class="literal"><SQL-data access indication> ::= NO SQL | |
| CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA</code> |
| </p> |
| <p> |
| <code class="literal"><null-call clause> ::= RETURNS NULL ON NULL |
| INPUT | CALLED ON NULL INPUT</code> |
| </p> |
| <p> |
| <code class="literal"><maximum returned result sets> ::= <unsigned |
| integer></code> |
| </p> |
| <p>Define an SQL-invoked routine.</p> |
| <a name="N112CA" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ALTER routine</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>alter routine statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><alter routine statement> ::= ALTER <specific |
| routine designator> <alter routine characteristics> <alter |
| routine behavior></code> |
| </p> |
| <p> |
| <code class="literal"><alter routine characteristics> ::= <alter |
| routine characteristic>...</code> |
| </p> |
| <p> |
| <code class="literal"><alter routine characteristic> ::= <language |
| clause> | <parameter style clause> | <SQL-data access |
| indication> | <null-call clause> | <returned result sets |
| characteristic> | NAME <external routine |
| name></code> |
| </p> |
| <p> |
| <code class="literal"><alter routine behavior> ::= |
| RESTRICT</code> |
| </p> |
| <p>Alter a characteristic of an SQL-invoked routine. Early |
| releases of HyperSQL 2.0 may not support this statement.</p> |
| <a name="N112E4" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop routine statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop routine statement> ::= DROP <specific |
| routine designator> <drop behavior></code> |
| </p> |
| <p>Destroy an SQL-invoked routine.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N112F5"></a>Sequence Creation</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N112F8" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CREATE SEQUENCE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>sequence generator definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><sequence generator definition> ::= CREATE |
| SEQUENCE <sequence generator name> [ <sequence generator |
| options> ]</code> |
| </p> |
| <p> |
| <code class="literal"><sequence generator options> ::= <sequence |
| generator option> ...</code> |
| </p> |
| <p> |
| <code class="literal"><sequence generator option> ::= <sequence |
| generator data type option> | <common sequence generator |
| options></code> |
| </p> |
| <p> |
| <code class="literal"><common sequence generator options> ::= |
| <common sequence generator option> ...</code> |
| </p> |
| <p> |
| <code class="literal"><common sequence generator option> ::= |
| <sequence generator start with option> | <basic sequence |
| generator option></code> |
| </p> |
| <p> |
| <code class="literal"><basic sequence generator option> ::= |
| <sequence generator increment by option> | <sequence generator |
| maxvalue option> | <sequence generator minvalue option> | |
| <sequence generator cycle option></code> |
| </p> |
| <p> |
| <code class="literal"><sequence generator data type option> ::= AS |
| <data type></code> |
| </p> |
| <p> |
| <code class="literal"><sequence generator start with option> ::= START |
| WITH <sequence generator start value></code> |
| </p> |
| <p> |
| <code class="literal"><sequence generator start value> ::= <signed |
| numeric literal></code> |
| </p> |
| <p> |
| <code class="literal"><sequence generator increment by option> ::= |
| INCREMENT BY <sequence generator increment></code> |
| </p> |
| <p> |
| <code class="literal"><sequence generator increment> ::= <signed |
| numeric literal></code> |
| </p> |
| <p> |
| <code class="literal"><sequence generator maxvalue option> ::= |
| MAXVALUE <sequence generator max value> | NO |
| MAXVALUE</code> |
| </p> |
| <p> |
| <code class="literal"><sequence generator max value> ::= <signed |
| numeric literal></code> |
| </p> |
| <p> |
| <code class="literal"><sequence generator minvalue option> ::= |
| MINVALUE <sequence generator min value> | NO |
| MINVALUE</code> |
| </p> |
| <p> |
| <code class="literal"><sequence generator min value> ::= <signed |
| numeric literal></code> |
| </p> |
| <p> |
| <code class="literal"><sequence generator cycle option> ::= CYCLE | NO |
| CYCLE</code> |
| </p> |
| <p>Define a named sequence generator. A SEQUENCE object generates |
| a sequence of integers according to the specified rules. The simple |
| definition without the options defines a sequence of numbers in INTEGER |
| type starting at 1 and incrementing by 1. By default the |
| <code class="literal">CYCLE</code> property is set and the minimum and maximum |
| limits are the minimum and maximum limits of the type of returned |
| values. There are self-explanatory options for changing various |
| properties of the sequence. The <code class="literal">MAXVALUE</code> and |
| <code class="literal">MINVALUE</code> specify the upper and lower limits. If |
| <code class="literal">CYCLE</code> is specified, after the sequence returns the |
| highest or lowest value in range, the next value will respectively be |
| the lowest or highest value in range. If <code class="literal">NO CYCLE</code> is |
| specified, the use of the sequence generator results in an error once |
| the limit has been reached.</p> |
| <p>The integer types: SMALLINT, INTEGER, BIGINT, DECIMAL and |
| NUMERIC can be used as the type of the sequence. DECIMAL and NUMERIC |
| types must have a scale of 0 and a precision not exceeding 18.</p> |
| <a name="N11347" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>ALTER SEQUENCE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>alter sequence generator |
| statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><alter sequence generator statement> ::= ALTER |
| SEQUENCE <sequence generator name> <alter sequence generator |
| options></code> |
| </p> |
| <p> |
| <code class="literal"><alter sequence generator options> ::= <alter |
| sequence generator option>...</code> |
| </p> |
| <p> |
| <code class="literal"><alter sequence generator option> ::= <alter |
| sequence generator restart option> | <basic sequence generator |
| option></code> |
| </p> |
| <p> |
| <code class="literal"><alter sequence generator restart option> ::= |
| RESTART [ WITH <sequence generator restart value> |
| ]</code> |
| </p> |
| <p> |
| <code class="literal"><sequence generator restart value> ::= |
| <signed numeric literal></code> |
| </p> |
| <p>Change the definition of a named sequence generator. The same |
| options that are used in the definition of the SEQUENCE can be used to |
| alter it. The exception is the option for the start value which is |
| <code class="literal">RESTART WITH</code> for the ALTER SEQUENCE |
| statement..</p> |
| <a name="N11367" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP SEQUENCE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop sequence generator |
| statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop sequence generator statement> ::= DROP |
| SEQUENCE [ IF EXISTS ] <sequence generator name> [ IF EXISTS ] |
| <drop behavior></code> |
| </p> |
| <p>Destroy an external sequence generator. If the |
| <code class="literal"><drop behavior></code> is <code class="literal">CASCADE</code>, |
| then all objects that reference the sequence are dropped. These objects |
| can be VIEW, ROUTINE or TRIGGER objects.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N1137F"></a>SQL Procedure Statement</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N11382" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>SQL procedure |
| statement</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>SQL procedure statement</em></span> |
| </p> |
| <p>The definition of CREATE TRIGGER and CREATE PROCEDURE |
| statements refers to <SQL procedure statement>. The definition of |
| this element is given below. However, only a subset of these statements |
| are allowed in trigger or routine definition.</p> |
| <p> |
| <code class="literal"><SQL procedure statement> ::= <SQL executable |
| statement></code> |
| </p> |
| <p> |
| <code class="literal"><SQL executable statement> ::= <SQL schema |
| statement> | <SQL data statement> | <SQL control |
| statement> | <SQL transaction statement> | <SQL connection |
| statement> | <SQL session statement> | <SQL diagnostics |
| statement> | <SQL dynamic statement></code> |
| </p> |
| <p> |
| <code class="literal"><SQL schema statement> ::= <SQL schema |
| definition statement> | <SQL schema manipulation |
| statement></code> |
| </p> |
| <p> |
| <code class="literal"><SQL schema definition statement> ::= <schema |
| definition> | <table definition> | <view definition> | |
| <SQL-invoked routine> | <grant statement> | <role |
| definition> | <domain definition> | <character set |
| definition> | <collation definition> | <transliteration |
| definition> | <assertion definition> | <trigger |
| definition> | <user-defined type definition> | <user-defined |
| cast definition> | <user-defined ordering definition> | |
| <transform definition> | <sequence generator |
| definition></code> |
| </p> |
| <p> |
| <code class="literal"><SQL schema manipulation statement> ::= <drop |
| schema statement> | <alter table statement> | <drop table |
| statement> | <drop view statement> | <alter routine |
| statement> | <drop routine statement> | <drop user-defined |
| cast statement> | <revoke statement> | <drop role |
| statement> | <alter domain statement> | <drop domain |
| statement> | <drop character set statement> | <drop |
| collation statement> | <drop transliteration statement> | |
| <drop assertion statement> | <drop trigger statement> | |
| <alter type statement> | <drop data type statement> | |
| <alter sequence generator statement> | <drop sequence generator |
| statement></code> |
| </p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N1139F"></a>Other Schema Object Creation</h3> |
| </div> |
| </div> |
| </div> |
| <a name="N113A2" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CREATE INDEX</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>create index statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><create index statement> ::= CREATE INDEX |
| <index name> ON <table name> <left paren> {<column |
| name> [ASC | DESC]}, ... <left paren></code> |
| </p> |
| <p>Creates an index on a group of columns of a table. The optional |
| [ASC | DESC] specifies if the column is indexed in the ascending or |
| descending order, but has no effect on how the index is created (it is |
| allowed for compatibility with other database engines). HyperSQL can use |
| all indexes in ascending or descending order as needed.</p> |
| <a name="N113B3" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP INDEX</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop index statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop index statement> ::= DROP INDEX [ IF |
| EXISTS ] <index name> [ IF EXISTS ]</code> |
| </p> |
| <p>Destroy an index.</p> |
| <a name="N113C4" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CREATE TYPE</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>user-defined type definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><user-defined type definition> ::= CREATE TYPE |
| <user-defined type body></code> |
| </p> |
| <p> |
| <code class="literal"><user-defined type body> ::= <schema-resolved |
| user-defined type name> [ AS <representation> |
| ]</code> |
| </p> |
| <p> |
| <code class="literal"><representation> ::= <predefined |
| type></code> |
| </p> |
| <p>Define a user-defined type. Currently only simple distinct |
| types can be defined without further attributes.</p> |
| <a name="N113DB" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CREATE CAST</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>user-defined cast definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><user-defined cast definition> ::= CREATE CAST |
| <left paren> <source data type> AS <target data type> |
| <right paren> WITH <cast function> [ AS ASSIGNMENT |
| ]</code> |
| </p> |
| <p> |
| <code class="literal"><cast function> ::= <specific routine |
| designator></code> |
| </p> |
| <p> |
| <code class="literal"><source data type> ::= <data |
| type></code> |
| </p> |
| <p> |
| <code class="literal"><target data type> ::= <data |
| type></code> |
| </p> |
| <p>Define a user-defined cast. This feature may be supported in a |
| future versions of HyperSQL.</p> |
| <a name="N113F5" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP CAST</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop user-defined cast statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop user-defined cast statement> ::= DROP CAST |
| <left paren> <source data type> AS <target data type> |
| <right paren> <drop behavior></code> |
| </p> |
| <p>Destroy a user-defined cast. This feature may be supported in a |
| future versions of HyperSQL.</p> |
| <a name="N11406" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CREATE CHARACTER SET</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>character set definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><character set definition> ::= CREATE CHARACTER |
| SET <character set name> [ AS ] <character set source> [ |
| <collate clause> ]</code> |
| </p> |
| <p> |
| <code class="literal"><character set source> ::= GET <character set |
| specification></code> |
| </p> |
| <p>Define a character set. A new CHARACTER SET is based on an |
| existing CHARACTER SET. The optional <code class="literal"><collate |
| clause></code> specifies the collation to be used, otherwise the |
| collation is inherited from the default collation for the source |
| CHARACTER SET.</p> |
| <a name="N1141D" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP CHARACTER SET</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop character set statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop character set statement> ::= DROP |
| CHARACTER SET <character set name></code> |
| </p> |
| <p>Destroy a character set. If the character set name is |
| referenced in any database object, the command fails. Note that |
| <code class="literal">CASCADE</code> or <code class="literal">RESTRICT</code> cannot be |
| specified for this command.</p> |
| <a name="N11434" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CREATE COLLATION</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>collation definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><collation definition> ::= CREATE COLLATION |
| <collation name> FOR <character set specification> FROM |
| <existing collation name> [ <pad characteristic> |
| ]</code> |
| </p> |
| <p> |
| <code class="literal"><existing collation name> ::= <collation |
| name></code> |
| </p> |
| <p> |
| <code class="literal"><pad characteristic> ::= NO PAD | PAD |
| SPACE</code> |
| </p> |
| <p>Define a collation. A new collation is based on an existing |
| COLLATION and applies to an existing CHARACTER SET. The <code class="literal"><pad |
| characteristic></code> specifies whether strings are padded with |
| spaces for comparison. This feature may be supported in a future |
| versions of HyperSQL.</p> |
| <a name="N1144E" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP COLLATION</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop collation statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop collation statement> ::= DROP COLLATION |
| <collation name> <drop behavior></code> |
| </p> |
| <p>Destroy a collation. If the <code class="literal"><drop |
| behavior></code> is <code class="literal">CASCADE</code>, then all |
| references to the collation revert to the default collation that would |
| be in force if the dropped collation was not specified. This feature may |
| be supported in a future versions of HyperSQL.</p> |
| <a name="N11465" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CREATE TRANSLATION</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>transliteration definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><transliteration definition> ::= CREATE |
| TRANSLATION <transliteration name> FOR <source character set |
| specification> TO <target character set specification> FROM |
| <transliteration source></code> |
| </p> |
| <p> |
| <code class="literal"><source character set specification> ::= |
| <character set specification></code> |
| </p> |
| <p> |
| <code class="literal"><target character set specification> ::= |
| <character set specification></code> |
| </p> |
| <p> |
| <code class="literal"><transliteration source> ::= <existing |
| transliteration name> | <transliteration |
| routine></code> |
| </p> |
| <p> |
| <code class="literal"><existing transliteration name> ::= |
| <transliteration name> </code> |
| </p> |
| <p> |
| <code class="literal"><transliteration routine> ::= <specific |
| routine designator></code> |
| </p> |
| <p>Define a character transliteration. This feature may be |
| supported in a future versions of HyperSQL.</p> |
| <a name="N11485" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP TRANSLATION</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop transliteration statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop transliteration statement> ::= DROP |
| TRANSLATION <transliteration name></code> |
| </p> |
| <p>Destroy a character transliteration. This feature may be |
| supported in a future versions of HyperSQL.</p> |
| <a name="N11496" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>CREATE ASSERTION</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>assertion definition</em></span> |
| </p> |
| <p> |
| <code class="literal"><assertion definition> ::= CREATE ASSERTION |
| <constraint name> CHECK <left paren> <search |
| condition> <right paren> [ <constraint characteristics> |
| ]</code> |
| </p> |
| <p>Specify an integrity constraint. This feature may be supported |
| in a future versions of HyperSQL.</p> |
| <a name="N114A7" class="indexterm"></a> |
| <p> |
| <span class="bold"><strong>DROP ASSERTION</strong></span> |
| </p> |
| <p> |
| <span class="emphasis"><em>drop assertion statement</em></span> |
| </p> |
| <p> |
| <code class="literal"><drop assertion statement> ::= DROP ASSERTION |
| <constraint name> [ <drop behavior> ]</code> |
| </p> |
| <p>Destroy an assertion. This feature may be supported in a future |
| versions of HyperSQL.</p> |
| </div> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h2 class="title" style="clear: both"> |
| <a name="N114B8"></a>The Information Schema</h2> |
| </div> |
| </div> |
| </div> |
| <p>The Information Schema is a special schema in each catalog. The SQL |
| Standard defines a number of character sets and domains in this schema. In |
| addition, all the implementation-defined collations belong to the |
| Information Schema.</p> |
| <p>The SQL Standard defines many views in the Information Schema. These |
| views show the properties of the database objects that currently exist in |
| the database. When a user accesses one these views, only the properties of |
| database objects that the user can access are included.</p> |
| <p>HyperSQL supports all the views defined by the Standard, apart from |
| a few views that report on extended user-defined types and other optional |
| features of the Standard that are not supported by HyperSQL.</p> |
| <p>HyperSQL also adds some views to the Information Schema. These views |
| are for features that are not reported in any of the views defined by the |
| Standard, or for use by JDBC DatabaseMetaData.</p> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N114C3"></a>Predefined Character Sets, Collations and Domains</h3> |
| </div> |
| </div> |
| </div> |
| <p>The SQL Standard defines a number of character sets and domains in |
| the INFORMATION SCHEMA.</p> |
| <p>These domains are used in the INFORMATION SCHEMA views:</p> |
| <p>CARDINAL_NUMBER, YES_OR_NO, CHARACTER_DATA, SQL_IDENTIFIER, |
| TIME_STAMP</p> |
| <p>All available collations are in the INFORMATION |
| SCHEMA.</p> |
| </div> |
| <div class="section" lang="en"> |
| <div class="titlepage"> |
| <div> |
| <div> |
| <h3 class="title"> |
| <a name="N114CE"></a>Views in INFORMATION SCHEMA</h3> |
| </div> |
| </div> |
| </div> |
| <p>The following views are defined by the SQL Standard:</p> |
| <p>ADMINISTRABLE_ROLE_AUTHORIZATIONS</p> |
| <p>APPLICABLE_ROLES</p> |
| <p>ASSERTIONS</p> |
| <p>AUTHORIZATIONS</p> |
| <p>CHARACTER_SETS</p> |
| <p>CHECK_CONSTRAINTS</p> |
| <p>CHECK_CONSTRAINT_ROUTINE_USAGE</p> |
| <p>COLLATIONS</p> |
| <p>COLUMNS</p> |
| <p>COLUMN_COLUMN_USAGE</p> |
| <p>COLUMN_DOMAIN_USAGE</p> |
| <p>COLUMN_PRIVILEGES</p> |
| <p>COLUMN_UDT_USAGE</p> |
| <p>CONSTRAINT_COLUMN_USAGE</p> |
| <p>CONSTRAINT_TABLE_USAGE</p> |
| <p>DATA_TYPE_PRIVILEGES</p> |
| <p>DOMAINS</p> |
| <p>DOMAIN_CONSTRAINTS</p> |
| <p>ENABLED_ROLES</p> |
| <p>INFORMATION_SCHEMA_CATALOG_NAME</p> |
| <p>KEY_COLUMN_USAGE</p> |
| <p>PARAMETERS</p> |
| <p>REFERENTIAL_CONSTRAINTS</p> |
| <p>ROLE_AUTHORIZATION_DESCRIPTORS</p> |
| <p>ROLE_COLUMN_GRANTS</p> |
| <p>ROLE_ROUTINE_GRANTS</p> |
| <p>ROLE_TABLE_GRANTS</p> |
| <p>ROLE_UDT_GRANTS</p> |
| <p>ROLE_USAGE_GRANTS</p> |
| <p>ROUTINE_COLUMN_USAGE</p> |
| <p>ROUTINE_JAR_USAGE</p> |
| <p>ROUTINE_PRIVILEGES</p> |
| <p>ROUTINE_ROUTINE_USAGE</p> |
| <p>ROUTINE_SEQUENCE_USAGE</p> |
| <p>ROUTINE_TABLE_USAGE</p> |
| <p>ROUTINES</p> |
| <p>SCHEMATA</p> |
| <p>SEQUENCES</p> |
| <p>SQL_FEATURES</p> |
| <p>SQL_IMPLEMENTATION_INFO</p> |
| <p>SQL_PACKAGES</p> |
| <p>SQL_PARTS</p> |
| <p>SQL_SIZING</p> |
| <p>SQL_SIZING_PROFILES</p> |
| <p>TABLES</p> |
| <p>TABLE_CONSTRAINTS</p> |
| <p>TABLE_PRIVILEGES</p> |
| <p>TRANSLATIONS</p> |
| <p>TRIGGERED_UPDATE_COLUMNS</p> |
| <p>TRIGGERS</p> |
| <p>TRIGGER_COLUMN_USAGE</p> |
| <p>TRIGGER_ROUTINE_USAGE</p> |
| <p>TRIGGER_SEQUENCE_USAGE</p> |
| <p>TRIGGER_TABLE_USAGE</p> |
| <p>USAGE_PRIVILEGES</p> |
| <p>USER_DEFINED_TYPES</p> |
| <p>VIEWS</p> |
| <p>VIEW_COLUMN_USAGE</p> |
| <p>VIEW_ROUTINE_USAGE</p> |
| <p>VIEW_TABLE_USAGE</p> |
| <p>The following views are specific to HyperSQL:</p> |
| <p>SYSTEM_BESTROWIDENTIFIER</p> |
| <p>SYSTEM_CACHEINFO</p> |
| <p>SYSTEM_COLUMNS</p> |
| <p>SYSTEM_COMMENTS</p> |
| <p>SYSTEM_CROSSREFERENCE</p> |
| <p>SYSTEM_INDEXINFO</p> |
| <p>SYSTEM_PRIMARYKEYS</p> |
| <p>SYSTEM_PROCEDURECOLUMNS</p> |
| <p>SYSTEM_PROCEDURES</p> |
| <p>SYSTEM_PROPERTIES</p> |
| <p>SYSTEM_SCHEMAS</p> |
| <p>SYSTEM_SEQUENCES</p> |
| <p>SYSTEM_SESSIONINFO</p> |
| <p>SYSTEM_SESSIONS</p> |
| <p>SYSTEM_TABLES</p> |
| <p>SYSTEM_TABLETYPES</p> |
| <p>SYSTEM_TEXTTABLES</p> |
| <p>SYSTEM_TYPEINFO</p> |
| <p>SYSTEM_UDTS</p> |
| <p>SYSTEM_USERS</p> |
| <p>SYSTEM_VERSIONCOLUMNS</p> |
| </div> |
| </div> |
| </div> |
| <HR xmlns:xi="http://www.w3.org/2001/XInclude"> |
| <P xmlns:xi="http://www.w3.org/2001/XInclude" class="svnrev">$Revision: 3601 $</P> |
| <div class="navfooter"> |
| <hr> |
| <table summary="Navigation footer" width="100%"> |
| <tr> |
| <td align="left" width="40%"><a accesskey="p" href="sessions-chapt.html"><img src="../images/db/prev.png" alt="Prev"></a> </td><td align="center" width="20%"> </td><td align="right" width="40%"> <a accesskey="n" href="texttables-chapt.html"><img src="../images/db/next.png" alt="Next"></a></td> |
| </tr> |
| <tr> |
| <td valign="top" align="left" width="40%">Chapter 3. Sessions and Transactions </td><td align="center" width="20%"><a accesskey="h" href="index.html"><img src="../images/db/home.png" alt="Home"></a></td><td valign="top" align="right" width="40%"> Chapter 5. Text Tables</td> |
| </tr> |
| </table> |
| </div> |
| </body> |
| </html> |
