The name of a schema object is an <identifier>. 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.

In addition to the name-spaces in the schema. Each table has a name-space for the names of its columns.

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 <identifier chain>. In some contexts, only a simple <identifier> can be used and the <identifier chain> is prohibited. While in some other contexts, the use of <identifier chain> 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 <catalog name>.<schema name>.<table name>.<column name>, likewise, a fully qualified sequence name is in the form <catalog name>.<schema name>.<sequence name>.

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.

A CHARACTER SET is the whole or a subset of the UNICODE character set.

A character set name can only be a <regular identifier>. There is a separate name-space for character sets.

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.

The following character sets have been specified by the SQL Standard:

SQL_TEXT, SQL_IDENTIFIER, SQL_CHARACTER, ASCII_GRAPHIC, GRAPHIC_IRV, ASCII_FULL, ISO8BIT, LATIN1, UTF32, UTF16, UTF8.

The ASCII_GRAPHIC is the same as GRAPHIC_IRV and ASCII_FULL is the same as ISO8BIT.

Most of the character sets are defined by well-known standards such as UNICODE.

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.

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.

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.

A COLLATION is the method used for ordering character strings in ordered sets and to determine equivalence of two character strings.

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.

There is a separate name-space for collations..

Collations for a large number of languages are supported by HyperSQL.

Early releases of HyperSQL version 2.0 only support a single collation for the whole database.

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.

Distinct types share a name-space with domains.

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.

Distinct types share a name-space with domains.

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.

There is a separate name-space for SEQUENCE objects.

IDENTITY columns are columns of tables which have an internal, unnamed SEQUENCE object.

SEQUENCE objects and IDENTITY columns are supported fully according to the latest SQL 2008 Standard syntax.

Sequences

The SQL:2008 syntax and usage is different from what is supported by many existing database engines. Sequences are created with the CREATE SEQUENCE command and their current value can be modified at any time with ALTER SEQUENCE. The next value for a sequence is retrieved with the NEXT VALUE FOR <name> expression. This expression can be used for inserting and updating table rows.

INSERT INTO mytable VALUES 2, 'John', NEXT VALUE FOR mysequence;

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:

SELECT NEXT VALUE FOR mysequence, col1, col2 FROM mytable WHERE ...

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.

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.

Identity Auto-Increment Columns

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.

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).

The SQL standard syntax is used, which allows the initial value and other options to be specified.

<colname> [ INTEGER | BIGINT | DECIMAL | NUMERIC ] GENERATED { BY DEFAULT | ALWAYS} AS IDENTITY [( <options> )] [PRIMARY KEY]

When you add a new row to such a table using an INSERT INTO <tablename> ... statement, you can use the DEFAULT keyword for the IDENTITY column, which results in an auto-generated value for the column. The IDENTITY() 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 CALL IDENTITY() 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 INSERT INTO <childtable> VALUES (...,IDENTITY(),...);. Both types of call to IDENTITY() must be made before any additional update or insert statements are issued by the session.

The last inserted IDENTITY value can also be retrieved via JDBC, by specifying the Statement or PreparedStatement object to return the generated value.

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:

ALTER TABLE ALTER COLUMN <column name> RESTART WITH <new value>;

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.

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')

In the SQL environment, tables are the most essential components, as they hold all persistent data.

If TABLE is considered as metadata (i.e. without its actual data) it is called a relation 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.

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.

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.

A view has many uses:

A VIEW that returns the columns of a single ordinary TABLE may be updatable. Some updatable views are insertable-into. 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.

Views share a name-space with tables.

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_".

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.

In a TABLE, a constraint takes three basic forms.

CHECK

A CHECK constraint consists of a <search condition> that must not be false (can be unknown) for each row of the table. The <search condition> can reference all the columns of the current row, and if it contains a <subquery>, other tables and views in the database (excluding its own table).

NOT NULL

A simple form of check constraint is the NOT NULL constraint, which applies to a single column.

UNIQUE

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

PRIMARY KEY

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.

FOREIGN KEY

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.

Constraints share a name space with assertions.

An ASSERTION is a top-level schema objects. It consists of a <search condition> that must not be false (can be unknown).

Assertions share a name-space with constraints

A TRIGGER is a child schema object that always belongs to a TABLE or a VIEW.

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.

Triggers are discussed in detail in chapter Triggers .

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.

There is a separate name-space for routines.

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 specific name, different from all other routine names and specific names in the schema. The specific name 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 specific name. This allows the user to drop one of the signatures while keeping the other.

Routines are discussed in detail in chapter SQL-Invoked Routines .

Indexes are an implementation-defined extension to the SQL Standard. HyperSQL has a dedicated name-space for indexes in each schema.

These elements and statements are used for different types of object. They are described here, before the statements that can use them.

identifier

definition of identifier

<identifier> ::= <regular identifier> | <delimited identifier> | <SQL language identifier>

<delimited identifier> ::= <double quote> <character sequence> <double quote>

<regular identifier> ::= <special character sequence>

<SQL language identifier> ::= <special character sequence>

A <delimited identifier> is a sequence of characters enclosed with double-quote symbols. All characters are allowed in the character sequence.

A <regular identifier> is a special sequence of characters. It consists of letters, digits and the underscore characters. It must begin with a letter.

A <SQL language identifier> is similar to <regular identifier> 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.

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 <regular identifier> the case-normal form is considered for comparison. This form consists of the upper-case of equivalent of all the letters.

The character sequence length of all identifiers must be between 1 and 128 characters.

A reserved word is one that is used by the SQL Standard for special purposes. It is similar to a <regular identifier> 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.

CASCADE or RESTRICT

drop behavior

<drop behavior> ::= CASCADE | RESTRICT

The <drop behavior> is a required element of statements that drop a SCHEMA or a schema object. If <drop behavior> is not specified then RESTRICT 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.

IF EXISTS

drop condition (HyperSQL)

<if exists clause> ::= IF EXISTS

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.

SPECIFIC

specific routine designator

<specific routine designator> ::= SPECIFIC <routine type> <specific name>

<routine type> ::= ROUTINE | FUNCTION | PROCEDURE

This clause is used in statements that need to specify one of the multiple versions of an overloaded routine. The <specific name> is the one specified in the <routine definition> statement.

RENAME

rename statement (HyperSQL)

<rename statement> ::= ALTER <object type> <name> RENAME TO <new name>

<object type> ::= CATALOG | SCHEMA | DOMAIN | TYPE | TABLE | CONSTRAINT | INDEX | ROUTINE | SPECIFIC ROUTINE

<column rename statement> ::= ALTER TABLE <table name> ALTER COLUMN <name> RENAME TO <new name>

This statement is used to rename an existing object. It is not part of the SQL Standard. The specified <name> is the existing name, which can be qualified with a schema name, while the <new name> is the new name for the object.

COMMENT

comment statement (HyperSQL)

<comment statement> ::= COMMENT ON { TABLE | COLUMN | ROUTINE } <name> IS <character string literal>

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. The <name> is the name of a table, view, column or routine. The name of the column consists of dot-separated <table name> . <column name>. 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.

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.

CREATE SCHEMA

schema definition

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 <schema definition> 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 <grant statement> and <role definition>. This is a curious aspect of the SQL standard, as these elements do not really belong to schema creation.

<schema definition> ::= CREATE SCHEMA <schema name clause> [ <schema character set specification> ] [ <schema element>... ]

<schema name clause> ::= <schema name> | AUTHORIZATION <authorization identifier> | <schema name> AUTHORIZATION <authorization identifier>

If the name of the schema is specified simply as <schema name>, then the AUTHORIZATION is the current user. Otherwise, the specified <authorization identifier> is used as the AUTHORIZATION for the schema. If <schema name> is omitted, then the name of the schema is the same as the specified <authorization identifier>.

<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>

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:

    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;

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.

DROP SCHEMA

drop schema statement

<drop schema statement> ::= DROP SCHEMA [ IF EXISTS ] <schema name> [ IF EXISTS ] <drop behavior>

This command destroys an existing schema. If <drop behavior> is RESTRICT, the schema must be empty, otherwise an error is raised. If CASCADE is specified, then all the objects contained in the schema are destroyed with a CASCADE option.

CREATE TABLE

table definition

<table definition> ::= CREATE [ { <table scope> | <table type> } ] TABLE <table name> <table contents source> [ ON COMMIT { PRESERVE | DELETE } ROWS ]

<table scope> ::= { GLOBAL | LOCAL } TEMPORARY

<table type> :: = MEMORY | CACHED

<table contents source> ::= <table element list> | <as subquery clause>

<table element list> ::= <left paren> <table element> [ { <comma> <table element> }... ] <right paren>

<table element> ::= <column definition> | <table constraint definition> | <like clause>

like clause

A <like clause> copies all column definitions from another table into the newly created table. Its three options indicate if the <default clause>, <identity column specification> and <generation clause> associated with the column definitions are copied or not. If an option is not specified, it defaults to EXCLUDING. The <generation clause> 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 <like clause> can be used multiple times, allowing the new table to have copies of the column definitions of one or more other tables.

CREATE TABLE t (id INTEGER PRIMARY KEY, LIKE atable INCLUDING DEFAULTS EXCLUDING IDENTITY)

<like clause> ::= LIKE <table name> [ <like options> ]

<like options> ::= <like option>...

<like option> ::= <identity option> | <column default option> | <generation option>

<identity option> ::= INCLUDING IDENTITY | EXCLUDING IDENTITY

<column default option> ::= INCLUDING DEFAULTS | EXCLUDING DEFAULTS

<generation option> ::= INCLUDING GENERATED | EXCLUDING GENERATED

as subquery clause

<as subquery clause> ::= [ <left paren> <column name list> <right paren> ] AS <table subquery> { WITH NO DATA | WITH DATA }

An <as subquery clause> used in table definition creates a table based on a <table subquery>. This kind of table definition is similar to a view definition. If WITH DATA is specified, then the new table will contain the rows of data returned by the <table subquery>.

CREATE TABLE t (a, b, c) AS (SELECT * FROM atable) WITH DATA

column definition

A column definition consists of a <column name> and in most cases a <data type> or <domain name> as minimum. The other elements of <column definition> are optional. Each <column name> in a table is unique.

<column definition> ::= <column name> [ <data type or domain name> ] [ <default clause> | <identity column specification> | <generation clause> ] [ <column constraint definition>... ] [ <collate clause> ]

<data type or domain name> ::= <data type> | <domain name>

<column constraint definition> ::= [ <constraint name definition> ] <column constraint> [ <constraint characteristics> ]

<column constraint> ::= NOT NULL | <unique specification> | <references specification> | <check constraint definition>

<identity column specification> ::= GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ <left paren> <common sequence generator options> <right paren> ]

<generation clause> ::= GENERATED ALWAYS AS <generation expression>

<generation expression> ::= <left paren> <value expression> <right paren>

The <identity column specification> can be specified for only a single column of the table.

A <column constraint definition> is a shortcut for a <table constraint definition>. 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.

The <identity column specification> 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:

CREATE TABLE t (id INTEGER GENERATED ALWAYS AS IDENTITY(START WITH 100), name VARCHAR(20) PRIMARY KEY, )

The <generation clause> is used for special columns which represent values based on the values held in other columns in the same row. The <value expression> 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 <query expression> is allowed. When <generation clause> is used, <data type> or <domain name> may be omitted.

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.

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'.

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)

DEFAULT

default clause

A default clause can be used if GENERATED is not specified. If a column has a <default clause> then it is possible to insert a row into the table without specifying a value for the column.

<default clause> ::= DEFAULT <default option>

<default option> ::= <literal> | <datetime value function> | USER | CURRENT_USER | CURRENT_ROLE | SESSION_USER | SYSTEM_USER | CURRENT_CATALOG | CURRENT_SCHEMA | CURRENT_PATH | NULL

The type of the <default option> must match the type of the column.

CONSTRAINT

constraint name and characteristics

<constraint name definition> ::= CONSTRAINT <constraint name>

<constraint characteristics> ::= <constraint check time> [ [ NOT ] DEFERRABLE [ <constraint check time> ] ]

<constraint check time> ::= INITIALLY DEFERRED | INITIALLY IMMEDIATE

Specify the name of a constraint and its characteristics. By default the constraint is NOT DEFERRABLE and INITIALLY IMMEDIATE. This means the constraint is enforced as soon as a data change statement is executed. If INITIALLY DEFERRED 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.

CONSTRAINT

table constraint definition

<table constraint definition> ::= [ <constraint name definition> ] <table constraint> [ <constraint characteristics> ]

<table constraint> ::= <unique constraint definition> | <referential constraint definition> | <check constraint definition>

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.

UNIQUE

unique constraint definition

<unique constraint definition> ::= <unique specification> <left paren> <unique column list> <right paren> | UNIQUE ( VALUE )

<unique specification> ::= UNIQUE | PRIMARY KEY

<unique column list> ::= <column name list>

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.

If UNIQUE( VALUE ) is specified, the constraint created on all columns of the table.

FOREIGN KEY

referential constraint definition

<referential constraint definition> ::= FOREIGN KEY <left paren> <referencing columns> <right paren> <references specification>

<references specification> ::= REFERENCES <referenced table and columns> [ MATCH <match type> ] [ <referential triggered action> ]

<match type> ::= FULL | PARTIAL | SIMPLE

<referencing columns> ::= <reference column list>

<referenced table and columns> ::= <table name> [ <left paren> <reference column list> <right paren> ]

<reference column list> ::= <column name list>

<referential triggered action> ::= <update rule> [ <delete rule> ] | <delete rule> [ <update rule> ]

<update rule> ::= ON UPDATE <referential action>

<delete rule> ::= ON DELETE <referential action>

<referential action> ::= CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION

A referential constraint allows links to be established between the rows of two tables. The specified list of <referencing columns> corresponds one by one to the columns of the specified list of <referenced columns> 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 <referenced columns>.

The [ MATCH match type ] 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 <referencing columns>. If the clause is not specified, MATCH SIMPLE is the default. If MATCH SIMPLE is specified, then any NULL means the row can exist (without a corresponding row in the referenced table). If MATCH FULL is specified then either all the column values must be NULL or none of them. MATCH PARTIAL allows any NULL but the non NULL values must match those of a row in the referenced table. HyperSQL does not support MATCH PARTIAL.

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.

CHECK

check constraint definition

<check constraint definition> ::= CHECK <left paren> <search condition> <right paren>

A CHECK constraint can exist for a TABLE or for a DOMAIN. The <search condition> evaluates to an SQL BOOLEAN value for each row of the table. Within the <search condition> all columns of the table row can be referenced. For all rows of the table, the <search condition> evaluates to TRUE or UNKNOWN. When a new row is inserted, or an existing row is updated, the <search condition> is evaluated and if it is FALSE, the insert or update fails.

A CHECK constraint for a DOMAIN is similar. In its <search condition>, the term VALUE is used to represents the value to which the DOMAIN applies.

CREATE TABLE t (a VARCHAR(20) CHECK (a IS NOT NULL AND CHARACTER_LENGTH(a) > 2))

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.

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.

SET TABLE writeability

set table write property (HyperSQL)

<set table read only statement> ::= SET TABLE <table name> { READ ONLY | READ WRITE }

Set the writeability property of a table. Tables are writable by default. This statement can be used to change the property between READ ONLY and READ WRITE. This is a feature of HyperSQL.

SET TABLE SOURCE

set table source statement

<set table source statement> ::= SET TABLE <table name> SOURCE <file and options> [DESC]

<file and options>::= <doublequote> <file path> [<semicolon> <property>...] <doublequote>

Set the text source for a text table. This statement cannot be used for tables that are not defined as TEXT TABLE.

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.

    SET TABLE mytable SOURCE 'myfile;fs=|;vs=.;lvs=~'

Only a user with the DBA role can execute this statement.

SET TABLE SOURCE HEADER

set table source header statement

<set table source header statement> ::= SET TABLE <table name> SOURCE HEADER <header string>

Set the header for the text source for a text table. If this command is used, the <header string> 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.

SET TABLE SOURCE on-off

set table source on-off statement

<set table source on-off statement> ::= SET TABLE <table name> SOURCE { ON | OFF }

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

ALTER TABLE

alter table statement

<alter table statement> ::= ALTER TABLE <table name> <alter table action>

<alter table action> ::= <add column definition> | <alter column definition> | <drop column definition> | <add table constraint definition> | <drop table constraint definition>

Change the definition of a table. Specific types of this statement are covered below.

ADD COLUMN

add column definition

<add column definition> ::= ADD [ COLUMN ] <column definition> [ BEFORE <other column name> ]

Add a column to an existing table. The <column definition> is specified the same way as it is used in <table definition>. HyperSQL allows the use of [ BEFORE <other column name> ] to specify at which position the new column is added to the table.

If the table contains rows, the new column must have a <default clause> or use one of the forms of GENERATED. The column values for each row is then filled with the result of the <default clause> or the generated value.

ALTER COLUMN

alter column definition

<alter column definition> ::= ALTER [ COLUMN ] <column name> <alter column action>

<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>

Change a column and its definition. Specific types of this statement are covered below. See also the RENAME statement above.

SET DEFAULT

set column default clause

<set column default clause> ::= SET <default clause>

Set the default clause for a column. This can be used if the column is not defined as GENERATED.

DROP DEFAULT

drop column default clause

<drop column default clause> ::= DROP DEFAULT

Drop the default clause from a column.

SET DATA TYPE

alter column data type clause

<alter column data type clause> ::= SET DATA TYPE <data type>

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.

alter identity column

alter identity column specification

<alter identity column specification> ::= <alter identity column option>...

<alter identity column option> ::= <alter sequence generator restart option> | SET <basic sequence generator option>

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.

SET NULL

alter column nullability

<alter column nullability> ::= SET NULL

Removes a NOT NULL constraint from a column. This option is specific to HyperSQL

DROP COLUMN

drop column definition

<drop column definition> ::= DROP [ COLUMN ] <column name> <drop behavior>

Destroy a column of a base table. The <drop behavior> is either RESTRICT or CASCADE. 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 RESTRICT is specified. If CASCADE is specified, then any CONSTRAINT, VIEW or TRIGGER object that references the column is dropped with a cascading effect.

ADD CONSTRAINT

add table constraint definition

<add table constraint definition> ::= ADD <table constraint definition>

Add a constraint to a table. The existing rows of the table must conform to the added constraint, otherwise the statement will not succeed.

DROP CONSTRAINT

drop table constraint definition

<drop table constraint definition> ::= DROP CONSTRAINT <constraint name> <drop behavior>

Destroy a constraint on a table. The <drop behavior> 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 CASCADE 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 CASCADE is specified.

DROP TABLE

drop table statement

<drop table statement> ::= DROP TABLE [ IF EXISTS ] <table name> [ IF EXISTS ] <drop behavior>

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 <drop behavior> is CASCADE, 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.

CREATE VIEW

view definition

<view definition> ::= CREATE [ RECURSIVE ] VIEW <table name> <view specification> AS <query expression> [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

<view specification> ::= [ <left paren> <view column list> <right paren> ]

<view column list> ::= <column name list>

Define a view. The <query expression> is a SELECT or similar statement. The <view column list> is the list of unique names for the columns of the view. The number of columns in the <view column list> must match the number of columns returned by the <query expression>. If <view column list> is not specified, then the columns of the <query expression> should have unique names and are used as the names of the view column.

Some views are updatable. As covered elsewhere, an updatable view is based on a single table or updatable view. For updatable views, the optional CHECK OPTION 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 WITH CASCADED CHECK OPTION is specified, then if the <query expression> of the view references another view, then the search condition of the underlying view should also be satisfied by the update or insert operation.

More on recursive...

DROP VIEW

drop view statement

<drop view statement> ::= DROP VIEW [ IF EXISTS ] <table name> [ IF EXISTS ] <drop behavior>

Destroy a view. The <drop behavior> is similar to dropping a table.

ALTER VIEW

alter view statement

<alter view statement> ::= ALTER VIEW <table name> <view specification> AS <query expression> [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

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.

CREATE DOMAIN

domain definition

<domain definition> ::= CREATE DOMAIN <domain name> [ AS ] <predefined type> [ <default clause> ] [ <domain constraint>... ] [ <collate clause> ]

<domain constraint> ::= [ <constraint name definition> ] <check constraint definition> [ <constraint characteristics> ]

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 <predefined type>, which is a base type defined by the Standard. It can have a <default clause>, 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.

CREATE DOMAIN valid_string AS VARCHAR(20) DEFAULT 'NO VALUE' CHECK (value IS NOT NULL AND CHARACTER_LENGTH(value) > 2) 

ALTER DOMAIN

alter domain statement

<alter domain statement> ::= ALTER DOMAIN <domain name> <alter domain action>

<alter domain action> ::= <set domain default clause> | <drop domain default clause> | <add domain constraint definition> | <drop domain constraint definition>

Change a domain and its definition.

SET DEFAULT

set domain default clause

<set domain default clause> ::= SET <default clause>

Set the default value in a domain.

DROP DEFAULT

drop domain default clause

<drop domain default clause> ::= DROP DEFAULT

Remove the default clause of a domain.

ADD CONSTRAINT

add domain constraint definition

<add domain constraint definition> ::= ADD <domain constraint>

Add a constraint to a domain.

DROP CONSTRAINT

drop domain constraint definition

<drop domain constraint definition> ::= DROP CONSTRAINT <constraint name>

Destroy a constraint on a domain. If the <drop behavior> is CASCADE, 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.

DROP DOMAIN

drop domain statement

<drop domain statement> ::= DROP DOMAIN <domain name> <drop behavior>

Destroy a domain. If <drop behavior> is CASCADE, 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.

CREATE TRIGGER

trigger definition

<trigger definition> ::= CREATE TRIGGER <trigger name> <trigger action time> <trigger event> ON <table name> [ REFERENCING <transition table or variable list> ] <triggered action>

<trigger action time> ::= BEFORE | AFTER | INSTEAD OF

<trigger event> ::= INSERT | DELETE | UPDATE [ OF <trigger column list> ]

<trigger column list> ::= <column name list>

<triggered action> ::= [ FOR EACH { ROW | STATEMENT } ] [ <triggered when clause> ] <triggered SQL statement>

<triggered when clause> ::= WHEN <left paren> <search condition> <right paren>

<triggered SQL statement> ::= <SQL procedure statement> | BEGIN ATOMIC { <SQL procedure statement> <semicolon> }... END | [QUEUE <integer literal>] [NOWAIT] CALL <HSQLDB trigger class FQN>

<transition table or variable list> ::= <transition table or variable>...

<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>

<old transition table name> ::= <transition table name>

<new transition table name> ::= <transition table name>

<transition table name> ::= <identifier>

<old transition variable name> ::= <correlation name>

<new transition variable name> ::= <correlation name>

Trigger definition is a relatively complex statement. The combination of <trigger action time> and <trigger event> determines the type of the trigger. Examples include BEFORE DELETE, AFTER UPDATE, INSTEAD OF INSERT. If the optional [ OF <trigger column list> ] is specified for an UPDATE trigger, then the trigger is activated only if one of the columns that is in the <trigger column list> is specified in the UPDATE statement that activates the trigger.

If a trigger is FOR EACH ROW, 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 FOR EACH STATEMENT, then a transient table is created containing all the rows for the before state and another transient table is created for the after state.

The [ REFERENCING <transition table or variable> ] is used to give a name to the before and after data row or table. This name can be referenced in the <SQL procedure statement> to access the data.

The optional <triggered when clause> 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.

The <SQL procedure statement> is limited to INSERT, DELETE, UPDATE and MERGE statements.

The <HSQLDB trigger class FQN> is a delimited identifier that contains the fully qualified name of a Java class that implements the org.hsqldb.Trigger interface.

Early releases of HyperSQL version 2.0 do not allow the use of OLD TABLE or NEW TABLE in statement level trigger definitions.

DROP TRIGGER

drop trigger statement

<drop trigger statement> ::= DROP TRIGGER <trigger name>

Destroy a trigger.

schema routine

SQL-invoked routine

<SQL-invoked routine> ::= <schema routine>

<schema routine> ::= <schema procedure> | <schema function>

<schema procedure> ::= CREATE <SQL-invoked procedure>

<schema function> ::= CREATE <SQL-invoked function>

<SQL-invoked procedure> ::= PROCEDURE <schema qualified routine name> <SQL parameter declaration list> <routine characteristics> <routine body>

<SQL-invoked function> ::= { <function specification> | <method specification designator> } <routine body>

<SQL parameter declaration list> ::= <left paren> [ <SQL parameter declaration> [ { <comma> <SQL parameter declaration> }... ] ] <right paren>

<SQL parameter declaration> ::= [ <parameter mode> ] [ <SQL parameter name> ] <parameter type> [ RESULT ]

<parameter mode> ::= IN | OUT | INOUT

<parameter type> ::= <data type>

<function specification> ::= FUNCTION <schema qualified routine name> <SQL parameter declaration list> <returns clause> <routine characteristics> [ <dispatch clause> ]

<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>

<routine characteristics> ::= [ <routine characteristic>... ]

<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>

<savepoint level indication> ::= NEW SAVEPOINT LEVEL | OLD SAVEPOINT LEVEL

<returned result sets characteristic> ::= DYNAMIC RESULT SETS <maximum returned result sets>

<parameter style clause> ::= PARAMETER STYLE <parameter style>

<dispatch clause> ::= STATIC DISPATCH

<returns clause> ::= RETURNS <returns type>

<returns type> ::= <returns data type> [ <result cast> ] | <returns table type>

<returns table type> ::= TABLE <table function column list>

<table function column list> ::= <left paren> <table function column list element> [ { <comma> <table function column list element> }... ] <right paren>

<table function column list element> ::= <column name> <data type>

<result cast> ::= CAST FROM <result cast from type>

<result cast from type> ::= <data type> [ <locator indication> ]

<returns data type> ::= <data type> [ <locator indication> ]

<routine body> ::= <SQL routine spec> | <external body reference>

<SQL routine spec> ::= [ <rights clause> ] <SQL routine body>

<rights clause> ::= SQL SECURITY INVOKER | SQL SECURITY DEFINER

<SQL routine body> ::= <SQL procedure statement>

<external body reference> ::= EXTERNAL [ NAME <external routine name> ] [ <parameter style clause> ]

<parameter style> ::= SQL | GENERAL

<deterministic characteristic> ::= DETERMINISTIC | NOT DETERMINISTIC

<SQL-data access indication> ::= NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA

<null-call clause> ::= RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

<maximum returned result sets> ::= <unsigned integer>

Define an SQL-invoked routine.

ALTER routine

alter routine statement

<alter routine statement> ::= ALTER <specific routine designator> <alter routine characteristics> <alter routine behavior>

<alter routine characteristics> ::= <alter routine characteristic>...

<alter routine characteristic> ::= <language clause> | <parameter style clause> | <SQL-data access indication> | <null-call clause> | <returned result sets characteristic> | NAME <external routine name>

<alter routine behavior> ::= RESTRICT

Alter a characteristic of an SQL-invoked routine. Early releases of HyperSQL 2.0 may not support this statement.

DROP

drop routine statement

<drop routine statement> ::= DROP <specific routine designator> <drop behavior>

Destroy an SQL-invoked routine.

CREATE SEQUENCE

sequence generator definition

<sequence generator definition> ::= CREATE SEQUENCE <sequence generator name> [ <sequence generator options> ]

<sequence generator options> ::= <sequence generator option> ...

<sequence generator option> ::= <sequence generator data type option> | <common sequence generator options>

<common sequence generator options> ::= <common sequence generator option> ...

<common sequence generator option> ::= <sequence generator start with option> | <basic sequence generator option>

<basic sequence generator option> ::= <sequence generator increment by option> | <sequence generator maxvalue option> | <sequence generator minvalue option> | <sequence generator cycle option>

<sequence generator data type option> ::= AS <data type>

<sequence generator start with option> ::= START WITH <sequence generator start value>

<sequence generator start value> ::= <signed numeric literal>

<sequence generator increment by option> ::= INCREMENT BY <sequence generator increment>

<sequence generator increment> ::= <signed numeric literal>

<sequence generator maxvalue option> ::= MAXVALUE <sequence generator max value> | NO MAXVALUE

<sequence generator max value> ::= <signed numeric literal>

<sequence generator minvalue option> ::= MINVALUE <sequence generator min value> | NO MINVALUE

<sequence generator min value> ::= <signed numeric literal>

<sequence generator cycle option> ::= CYCLE | NO CYCLE

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 CYCLE 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 MAXVALUE and MINVALUE specify the upper and lower limits. If CYCLE 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 NO CYCLE is specified, the use of the sequence generator results in an error once the limit has been reached.

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.

ALTER SEQUENCE

alter sequence generator statement

<alter sequence generator statement> ::= ALTER SEQUENCE <sequence generator name> <alter sequence generator options>

<alter sequence generator options> ::= <alter sequence generator option>...

<alter sequence generator option> ::= <alter sequence generator restart option> | <basic sequence generator option>

<alter sequence generator restart option> ::= RESTART [ WITH <sequence generator restart value> ]

<sequence generator restart value> ::= <signed numeric literal>

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 RESTART WITH for the ALTER SEQUENCE statement..

DROP SEQUENCE

drop sequence generator statement

<drop sequence generator statement> ::= DROP SEQUENCE [ IF EXISTS ] <sequence generator name> [ IF EXISTS ] <drop behavior>

Destroy an external sequence generator. If the <drop behavior> is CASCADE, then all objects that reference the sequence are dropped. These objects can be VIEW, ROUTINE or TRIGGER objects.

SQL procedure statement

SQL procedure statement

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.

<SQL procedure statement> ::= <SQL executable statement>

<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>

<SQL schema statement> ::= <SQL schema definition statement> | <SQL schema manipulation statement>

<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>

<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>

CREATE INDEX

create index statement

<create index statement> ::= CREATE INDEX <index name> ON <table name> <left paren> {<column name> [ASC | DESC]}, ... <left paren>

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.

DROP INDEX

drop index statement

<drop index statement> ::= DROP INDEX [ IF EXISTS ] <index name> [ IF EXISTS ]

Destroy an index.

CREATE TYPE

user-defined type definition

<user-defined type definition> ::= CREATE TYPE <user-defined type body>

<user-defined type body> ::= <schema-resolved user-defined type name> [ AS <representation> ]

<representation> ::= <predefined type>

Define a user-defined type. Currently only simple distinct types can be defined without further attributes.

CREATE CAST

user-defined cast definition

<user-defined cast definition> ::= CREATE CAST <left paren> <source data type> AS <target data type> <right paren> WITH <cast function> [ AS ASSIGNMENT ]

<cast function> ::= <specific routine designator>

<source data type> ::= <data type>

<target data type> ::= <data type>

Define a user-defined cast. This feature may be supported in a future versions of HyperSQL.

DROP CAST

drop user-defined cast statement

<drop user-defined cast statement> ::= DROP CAST <left paren> <source data type> AS <target data type> <right paren> <drop behavior>

Destroy a user-defined cast. This feature may be supported in a future versions of HyperSQL.

CREATE CHARACTER SET

character set definition

<character set definition> ::= CREATE CHARACTER SET <character set name> [ AS ] <character set source> [ <collate clause> ]

<character set source> ::= GET <character set specification>

Define a character set. A new CHARACTER SET is based on an existing CHARACTER SET. The optional <collate clause> specifies the collation to be used, otherwise the collation is inherited from the default collation for the source CHARACTER SET.

DROP CHARACTER SET

drop character set statement

<drop character set statement> ::= DROP CHARACTER SET <character set name>

Destroy a character set. If the character set name is referenced in any database object, the command fails. Note that CASCADE or RESTRICT cannot be specified for this command.

CREATE COLLATION

collation definition

<collation definition> ::= CREATE COLLATION <collation name> FOR <character set specification> FROM <existing collation name> [ <pad characteristic> ]

<existing collation name> ::= <collation name>

<pad characteristic> ::= NO PAD | PAD SPACE

Define a collation. A new collation is based on an existing COLLATION and applies to an existing CHARACTER SET. The <pad characteristic> specifies whether strings are padded with spaces for comparison. This feature may be supported in a future versions of HyperSQL.

DROP COLLATION

drop collation statement

<drop collation statement> ::= DROP COLLATION <collation name> <drop behavior>

Destroy a collation. If the <drop behavior> is CASCADE, 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.

CREATE TRANSLATION

transliteration definition

<transliteration definition> ::= CREATE TRANSLATION <transliteration name> FOR <source character set specification> TO <target character set specification> FROM <transliteration source>

<source character set specification> ::= <character set specification>

<target character set specification> ::= <character set specification>

<transliteration source> ::= <existing transliteration name> | <transliteration routine>

<existing transliteration name> ::= <transliteration name>

<transliteration routine> ::= <specific routine designator>

Define a character transliteration. This feature may be supported in a future versions of HyperSQL.

DROP TRANSLATION

drop transliteration statement

<drop transliteration statement> ::= DROP TRANSLATION <transliteration name>

Destroy a character transliteration. This feature may be supported in a future versions of HyperSQL.

CREATE ASSERTION

assertion definition

<assertion definition> ::= CREATE ASSERTION <constraint name> CHECK <left paren> <search condition> <right paren> [ <constraint characteristics> ]

Specify an integrity constraint. This feature may be supported in a future versions of HyperSQL.

DROP ASSERTION

drop assertion statement

<drop assertion statement> ::= DROP ASSERTION <constraint name> [ <drop behavior> ]

Destroy an assertion. This feature may be supported in a future versions of HyperSQL.

The SQL Standard defines a number of character sets and domains in the INFORMATION SCHEMA.

These domains are used in the INFORMATION SCHEMA views:

CARDINAL_NUMBER, YES_OR_NO, CHARACTER_DATA, SQL_IDENTIFIER, TIME_STAMP

All available collations are in the INFORMATION SCHEMA.

The following views are defined by the SQL Standard:

ADMINISTRABLE_ROLE_AUTHORIZATIONS

APPLICABLE_ROLES

ASSERTIONS

AUTHORIZATIONS

CHARACTER_SETS

CHECK_CONSTRAINTS

CHECK_CONSTRAINT_ROUTINE_USAGE

COLLATIONS

COLUMNS

COLUMN_COLUMN_USAGE

COLUMN_DOMAIN_USAGE

COLUMN_PRIVILEGES

COLUMN_UDT_USAGE

CONSTRAINT_COLUMN_USAGE

CONSTRAINT_TABLE_USAGE

DATA_TYPE_PRIVILEGES

DOMAINS

DOMAIN_CONSTRAINTS

ENABLED_ROLES

INFORMATION_SCHEMA_CATALOG_NAME

KEY_COLUMN_USAGE

PARAMETERS

REFERENTIAL_CONSTRAINTS

ROLE_AUTHORIZATION_DESCRIPTORS

ROLE_COLUMN_GRANTS

ROLE_ROUTINE_GRANTS

ROLE_TABLE_GRANTS

ROLE_UDT_GRANTS

ROLE_USAGE_GRANTS

ROUTINE_COLUMN_USAGE

ROUTINE_JAR_USAGE

ROUTINE_PRIVILEGES

ROUTINE_ROUTINE_USAGE

ROUTINE_SEQUENCE_USAGE

ROUTINE_TABLE_USAGE

ROUTINES

SCHEMATA

SEQUENCES

SQL_FEATURES

SQL_IMPLEMENTATION_INFO

SQL_PACKAGES

SQL_PARTS

SQL_SIZING

SQL_SIZING_PROFILES

TABLES

TABLE_CONSTRAINTS

TABLE_PRIVILEGES

TRANSLATIONS

TRIGGERED_UPDATE_COLUMNS

TRIGGERS

TRIGGER_COLUMN_USAGE

TRIGGER_ROUTINE_USAGE

TRIGGER_SEQUENCE_USAGE

TRIGGER_TABLE_USAGE

USAGE_PRIVILEGES

USER_DEFINED_TYPES

VIEWS

VIEW_COLUMN_USAGE

VIEW_ROUTINE_USAGE

VIEW_TABLE_USAGE

The following views are specific to HyperSQL:

SYSTEM_BESTROWIDENTIFIER

SYSTEM_CACHEINFO

SYSTEM_COLUMNS

SYSTEM_COMMENTS

SYSTEM_CROSSREFERENCE

SYSTEM_INDEXINFO

SYSTEM_PRIMARYKEYS

SYSTEM_PROCEDURECOLUMNS

SYSTEM_PROCEDURES

SYSTEM_PROPERTIES

SYSTEM_SCHEMAS

SYSTEM_SEQUENCES

SYSTEM_SESSIONINFO

SYSTEM_SESSIONS

SYSTEM_TABLES

SYSTEM_TABLETYPES

SYSTEM_TEXTTABLES

SYSTEM_TYPEINFO

SYSTEM_UDTS

SYSTEM_USERS

SYSTEM_VERSIONCOLUMNS