Using VoltDB

contents

CREATE TABLE

CREATE TABLE — Creates a table in the database.

Synopsis

CREATE TABLE table-name (
column-definition [,...]
[, key-definition [,...]]

);

column-definition: column-name datatype [UNIQUE] [DEFAULT value ] [ NULL | NOT NULL ]

key-definition: [CONSTRAINT constraint-name] PRIMARY KEY (column-name [,...])

Description

The CREATE TABLE statement creates a table and its associated columns in the database. The supported datatypes are described in Table A.1, “Supported SQL Datatypes”.

Table A.1. Supported SQL Datatypes

SQL DatatypeEquivalent Java DatatypeDescription
TINYINTbyte1-byte signed integer, -127 to 127
SMALLINTshort2-byte signed integer, -32,767 to 32,767
INTEGERint4-byte signed integer, -2,147,483,647 to 2,147,483,647
BIGINTlong8-byte signed integer, -9,223,372,036,854,775,807 to 9,223,372,036,854,775,807
FLOATdouble8-byte numeric, -(2-2-52)·21023 to (2-2-52)·21023 (Note that values less than or equal to -1.7E+308 are interpreted as null.)
DECIMALBigDecimal16-byte fixed scale of 12 and precision of 38, -99999999999999999999999999.999999999999 to 99999999999999999999999999.999999999999
VARCHAR()StringVariable length text string
VARBINARY()byte arrayVariable length binary string (sometimes referred to as a "blob")
TIMESTAMPlong, VoltDB TimestampTypeTime in microseconds

The following limitations are important to note when using the CREATE TABLE statement in VoltDB:

  • CHECK and FOREIGN KEY constraints are not supported.

  • VoltDB does not support AUTO_INCREMENT, the automatic incrementing of column values.

  • Each column has a maximum size of one megabyte and the sum of the actual content in the columns for any given row cannot exceed two megabytes.

  • If you intend to use a column to partition a table, that column cannot contain null values. You must specify NOT NULL in the definition of the column or VoltDB issues an error when compiling the schema.

  • When you specify a primary key, by default VoltDB creates a tree index. You can explicitly create a hash index by including the string "hash" as part of the index name. For example, the following declaration creates a hash index, Version_Hash_Idx, of three numeric columns.

    CREATE TABLE Version (
    Major SMALLINT NOT NULL,
    Minor SMALLINT NOT NULL,
    baselevel INTEGER NOT NULL,
    ReleaseDate TIMESTAMP,
    CONSTRAINT Version_Hash_Idx PRIMARY KEY
        (Major, Minor, Baselevel)
);

    See the description of CREATE INDEX for more information on the difference between hash and tree indexes.

  • For integer and floating-point datatypes, the largest possible negative value is reserved by VoltDB and interpreted as null.

  • The VARBINARY datatype provides variable storage for arbitrary strings of binary data and operates similarly to VARCHAR strings. You assign byte arrays to a VARBINARY column when passing in variables, or you can use a hexidecimal string for assigning literal values in the SQL statement. However, VARBINARY columns cannot be used in indexes or in conditional comparisons (such as in SELECT ... WHERE statements).

  • The VoltDB Timestamp datatype is a long integer representing the number of microseconds since the epoch. Two important points to note about this timestamp:

    • The VoltDB Timestamp is not the same as the Java Timestamp datatype or traditional Linux time measurements, which are measured in milliseconds rather than microseconds. Appropriate conversion is needed when casting values between a VoltDB timestamp and other timestamp datatypes.

    • The VoltDB Timestamp is interpreted as a Greenwich Meantime (GMT) value. Depending on how time values are created, their value may or may not account for the local machine's default time zone. Mixing timestamps from different time zones (for example, in WHERE clause comparisons) can result in unexpected behavior.

contents