Column Definitions

Syntax

column_def:
        column_name data_type [col_default] [[col_constraint] [col_constraint]...]
    |   column_name ROWID [[rowid_constraint] [rowid_constraint]...]

Description

A table is comprised of one or more column definitions. Each column definition must follow the syntax shown above.

The name of the column is given by column_name, which is a case-insensitive identifier by default. If case needs to be preserved, the name should be defined in double quotations.

Duplicate column names are not allowed in a table, even if the case is different.

A good practice when naming columns is to use the same names for the primary and foreign key columns. Keeping all other column names unique across all the tables in the database will allow you to use the natural join operation in your select statements.

Components

column_name	The name of a column to be created in the new table.
data_type	The data type of the column. This can include array specifiers. For more information on the data types supported, refer to the Data Types section.
col_default	The DEFAULT attribute can be used to specify a default value for a column when one has not been provided on an insert statement for the table. For more information on column defaults, refer to the DEFAULT Values section.
col_constraint	Optional column constraints that can be declared (keys, foreign keys, or check clauses). For more information on column constraints, refer to the Column Constraint Declarations section.
rowid_constraint	Optional rowid constraints that can be declared.

Examples

CREATE TABLE sensor
(
    name CHAR(31) UNIQUE KEY NOT NULL,
    status INT32 DEFAULT 0 NOT NULL
);

CREATE TABLE measurement
(
    Mtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    Mvalue INT32 DEFAULT 0 NOT NULL,
    raw_data BLOB DEFAULT x'' NOT NULL,
    sensor_measurement_name CHAR(31) CONSTRAINT sensor_measurement REFERENCES sensor(name) ON UPDATE CASCADE,

    CONSTRAINT k_reading KEY (
        Mtime ASC,
        Mvalue ASC
    )
);