Table definitions

Table definitions mk Sat, 03/27/2010 - 22:34

The CREATE TABLE statement conforms to the full SQL standard. Tables are assigned to the current schema unless the schema name is explicitly given as part of the table name. Table names should be unique amongst those mentioned within the same schema and distinct from index-, contraint-x, and view-names.

table_def:
     CREATE TABLE table_name table_content_source
   | CREATE MERGE TABLE table_name table_content_source
   | CREATE REPLICA TABLE table_name table_content_source
   | CREATE REMOTE TABLE table_name table_content_source ON STRING

table_content_source:
   '(' table_element_list ') 
  |  ['(' column_name_list ')' ] AS select_query  { WITH NO DATA | WITH DATA }

The derived (temporary) tables are either filled upon creation or automatically upon use within queries.

table_def:
   | CREATE [LOCAL | GLOBAL] TEMPORARY  TABLE table_name '(' table_content_source ')'
                     [ ON COMMIT [ DELETE ROWS |  PRESERVE ROWS |  DROP ] ]

Temporary local tables are limited to the client session. They are stored automatically under the schema 'tmp'. The qualifiers denote the actions taken during transaction commit over a temporary table. If the ON COMMIT clause is omitted then all tuples are dropped while retaining the structure.

Table elements

Table elements mk Sun, 03/28/2010 - 13:46
table_element:
    column_name data_type [column_option ...]
  | CONSTRAINT constraint_name table_constraint_type
  | column_name WITH OPTIONS '(' column_option ','... ')'
  | LIKE table_name
  | column_name SERIAL
  | column_name BIGSERIAL

Identity columns take their values from a sequence generator. The MySQL (auto_increment) and PostgreSQL (serial data type) syntax are also supported. The SERIAL type is mapped to either an INTEGER or BIGINT (when using BIGSERIAL).

Column and Table constraints are both supported. Besides the simple not null check also unique, primary, and foreign keys are supported. We currently check constraints directly on insert, update and delete, so immediate. This limitation stems from the missing triggers. The null matching on foreign keys is limited to the simple match type (null values satisfy the constraint). The full and partial match types are not supported. The referential action is currently limited to restrict, i.e. an update fails if other columns have references to it. The CHECK constraints are currently not enforced. Also they are not recorded in a data dictionary table.

table_constraint_type:
    UNIQUE '(' column_name ',' ... ')'
   | PRIMARY KEY '(' column_name ',' ... ')'
   | CHECK '(' column_logical_expression ')'
   | FOREIGN KEY '(' column_name ',' ... ')' REFERENCES table_name ['(' column_name ',' ... ')'] [ match_options ] [ ref_actions ]

A table constraint definition is not tied to a particular column, but rather to a column group. A column constraint is a notational convenience when the constraint only affects one column.

column_option:
    DEFAULT default_value
   | [ constraint_name ] column_constraint_type
   | GENERATED ALWAYS AS IDENTITY [ '(' serial_parameters ')' ]
   | AUTO_INCREMENT

 

column_constraint_type:
    NOT NULL | NULL | UNIQUE | PRIMARY KEY | CHECK '(' column_logical_expression ')'
  | REFERENCES table_name [ '(' column_name ',' ... ')' ] [ match_options ] [ ref_actions ]

match_options:
    MATCH { FULL | PARTIAL | SIMPLE }

ref_actions:
     { ON UPDATE | ON DELETE } { NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT }

A constraint over a referenced value can be either a FULL, PARTIAL or SIMPLE (default) match. A ful match requires all column values to be not null unless all are null.The simple match is more relaxed; any of the keys may be null. The partial match is consider noise for the time being.

The serial columns are a slight extension over the serial types defined earlier. In particular, we can designate RESTART option when the sequence is exhausted. This may involve a subquery over the database.

serial_parameters:
     [ START WITH nonzero-bigint ]
   | [ RESTART | RESTART WITH subquery
   | RESTART WITH nonzero-bigint ]
   | [INCREMENT BY nonzero-bigint ]
   | [MINVALUE nonzero-bigint | NOMINVALUE]
   | [MAXVALUE nonzero-bigint | NOMAXVALUE ]
   | [CACHE nonzero-bigint ]
   | [CYCLE | NOCYCLE]

Index definitions

Index definitions mk Sat, 03/27/2010 - 22:46

The index statements in the SQL standard are recognized, but their implementation is different from competitive products. MonetDB/SQL interprets these statements as an advice and often freely neglects it, relying on its own decision to create and maintain indexes for fast access. Also the UNIQUE qualifier is not honored or enforced. To add a uniqueness constraint for a table or column use UNIQUE in the CREATE TABLE or ALTER TABLE statement.

index_def:
    CREATE [ UNIQUE ] INDEX ident ON [ schema name . ] table name '(' ident ','... ')'

 

MonetDB supports two special kinds of secondary indices: IMPRINTS and ORDERED INDEX, introduced in release Dec2016 (v11.25.3). These index types are experimental and have some limitations: Only 1 column can be indexed per index. Only columns of numeric data type (tinyint, smallint, integer, bigint, hugeint, float, real, double, decimal) can be indexed. The index is not maintained automatically and will become inactive when inserts, deletes or updates are done on the column data. Since creating these indices can be expensive, they are only used when explicitly created. They are useful for large static (or read only) tables with numeric columns.

index_def:
    CREATE IMPRINTS INDEX ident ON [ schema name . ] table name '(' numeric column name ')'

Creates a new imprints index on one numerical column of a specific table. This index stores meta data (min, max, nulls) on segments of column data. It can speed-up queries which have a column selection condition (such as: AGE IS NULL OR AGE >= 18). It is a very compact index.

See also: Documentation/Manuals/MonetDB/Kernel/Modules/Imprints

index_def:
    CREATE ORDERED INDEX ident ON [ schema name . ] table name '(' numeric column name ')'

Creates a new ordered index on one numerical column of a specific table. This index is a special single column index where the numeric values are stored in ascending order. It can speed-up queries which have a column selection condition (such as: AGE BETWEEN 18 AND 30) or need sorting such as when used in a GROUP BY clause.

Alter statement

Alter statement mk Sat, 03/27/2010 - 22:39

The ALTER statement can be used to change the TABLE properties, it requires authorization to do so. Addition of a column follows the same syntax and functionality as the CREATE TABLE statement. This form can be used to remove individual columns from a table. If the table is used in a foreign key relationship the actions to be take can be further specified as RESTRICT to forbid it if there are external references to the column values. The CASCADE nullifies all references to the values being removed.

alter_statement:

         ALTER TABLE qname ADD    [COLUMN] { column_def | table_constraint }
      |  ALTER TABLE qname ALTER [COLUMN] ident SET DEFAULT value
      |  ALTER TABLE qname ALTER [COLUMN] ident SET [NOT] NULL
      |  ALTER TABLE qname ALTER [COLUMN] ident DROP DEFAULT
      |  ALTER TABLE qname ALTER [COLUMN] ident SET STORAGE {string | NULL}
      |  ALTER TABLE qname DROP [COLUMN] ident [RESTRICT | CASCADE]
      |  ALTER TABLE qname DROP CONSTRAINT ident [RESTRICT | CASCADE]
 

An individual table can be protected using the READ ONLY and INSERT ONLY mode. All attempts to update are flagged as a SQL error. The reverse operation is ALTER TABLE qname READ WRITE, which makes the table accessible for all update operations.

        | ALTER TABLE qname SET { { READ | INSERT } ONLY | READ WRITE }

The ALTER statement has been extended with ADD TABLE and DROP TABLE options to allow adding/removing partition tables to/from a MERGE TABLE.

   | ALTER TABLE qname ADD TABLE qname
   | ALTER TABLE qname DROP TABLE ident [ RESTRICT | CASCADE ]