CREATE TABLE

CREATE TABLE
Prev	Appendix A. Table Data Definition Language Overview	Next

Field Definitions
Supported Data Types
Field Constraints
Table Creation Examples

To create a table definition, use a CREATE TABLE statement. Its form is:

CREATE TABLE [IF NOT EXISTS] table-name (
    field-definition, field-definition-2 ..., 
    PRIMARY KEY (field-name, field-name-2...),
    [COMMENT "comment string"]
)

where:

IF NOT EXISTS is optional, and it causes table creation to be silently skipped if a table of the given name already exists in the store, and the table's definition exactly matches the provided definition. No error is returned as a result of the statement execution.

If this statement phrase is not specified, then an attempt to duplicate a table name in the store results in a failed table creation.
table-name is the name of the table. This field is required. If you are creating a sub-table, then use dot notation. For example, a table might be named Users. You might then define a sub-table named Users.MailingAddress.
field-definition is a comma-separated list of fields. There are one or more field definitions for every table. Field definitions are described next in this section.
PRIMARY KEY identifies at least one field in the table as the primary key. A primary key definition is required for every table. For information on primary keys, see Primary Keys.

To define a shard key (optional), use the SHARD keyword in the primary key statement. For information on shard keys, see Shard Keys.

For example:
```
PRIMARY KEY (SHARD(id), lastName)
```
COMMENT is optional. You can use this to provide a brief description of the table. The comment will not be interpreted but it is included in the table's metadata.

Field Definitions

When defining a table, field definitions take the form:

field-name type [constraints] [COMMENT "comment-string"]

where:

field-name is the name of the field. For example: id or familiarName. Every field must have a name.
type describes the field's data type. This can be a simple type such as INTEGER or STRING, or it can be a complex type such a RECORD. The list of allowable types is described in the next section.
constraints describes any limits placed on the data contained in the field. That is, minimum or maximum values, allowable ranges, or default values. This information is optional. See Field Constraints for more information.
COMMENT is optional. You can use this to provide a brief description of the field. The comment will not be interpreted but it is included in the table's metadata.

Supported Data Types

The following data types are supported for table fields:

ARRAY

An array of data. All elements of the array must be of the same data type, and this type must be declared when you define the array field. For example, to define an array of strings:
```
myArray ARRAY(STRING)
```
Note that field constraints can be applied to array value. For example:
```
myArray ARRAY(INTEGER CHECK(ELEMENTOF(myArray) > 0 and \
ELEMENTOF(myArray) < 100))
```
See CHECK for a description of the CHECK statement.
BINARY

Binary data.
BINARY(length)

Fixed-length binary field of size length (in bytes).
BOOLEAN

A boolean data type.
DOUBLE

A double.
ENUM

An enumerated list. The field definition must provide the list of allowable enumerated values. For example:
```
fruitName ENUM(apple,pear,orange)
```
FLOAT

A float.
INTEGER

An integer.
LONG

A long.
MAP

A data map. All map keys are strings, but when defining these fields you must define the data type of the data portion of the map. For example, if your keys map to integer values, then you define the field like this:
```
myMap MAP(INTEGER)
```
Note that field constraints can be applied to mapped value. For example:
```
myMap MAP(INTEGER CHECK(ELEMENTOF(myMap) > 0 and \
ELEMENTOF(myMap) < 13))
```
See CHECK for a description of the CHECK statement.
RECORD

An embedded record. This field definition must define all the fields contained in the embedded record. All of the same syntax rules apply as are used for defining an ordinary table field. For example, a simple embedded record might be defined as:
```
myEmbeddedRecord RECORD(firstField STRING, secondField INTEGER)
```
Data constraints, default values, and so forth can also be used with the embedded record's field definitions.
STRING

A string.

Field Constraints

Field constraints are used to define information about the field, such as the allowable range of values and default values. For example:

day_of_month CHECK (day_of_month >= 1 AND day_of_month <= 31)

Not all data type support constraints, and individual data types do not support all possible constraints.

CHECK

Use CHECK to specify an allowable range of values. The symbols AND, <, <=, >, and >= are all supported. <= and >= specifying inclusive ranges, and < and > specify exclusive ranges. For example:

myInt INTEGER CHECK(myInt > 10 and myInt < 20)

For simple data types, (INTEGER, LONG, FLOAT, DOUBLE, STRING), use the field's name to specify the range, as shown in the previous example.

For STRING datatypes, the range specifies the allowable string length.

For MAP and ARRAY datatypes, CHECK can be used to constraint the range of allowable values. Use ELEMENTOF() to refer to the MAP's or ARRAY's value. For example:

myMap MAP(INTEGER CHECK(ELEMENTOF(myMap) > 10))

or:

myArray ARRAY(INTEGER CHECK(ELEMENTOF(myArray) > 100 AND \
ELEMENTOF(myArray) < 1000))

CHECK is not supported for BINARY, BOOLEAN, ENUM, or RECORD datatypes, although CHECK is supported for the individual fields defined by RECORD:

myRec RECORD(a STRING, b INTEGER CHECK(b >= 0 AND b <= 10))

COMMENT

All data types can accept a COMMENT as part of their constraint. COMMENT strings are not parsed, but do become part of the table's metadata. For example:

myRec RECORD(a STRING, b INTEGER) COMMENT "Comment string"

myInt INTEGER CHECK(myInt > 10 and myInt < 20) COMMENT "Comment string"

DEFAULT

All fields can accept a DEFAULT constraint, except for ARRAY, BINARY, MAP, and RECORD. The value specified by DEFAULT is used in the event that the field data is not specified when the table is written to the store.

For example:

id INTEGER DEFAULT -1,
description STRING DEFAULT "NONE",
size ENUM(small,medium,large) DEFAULT medium,
inStock BOOLEAN DEFAULT FALSE

NOT NULL

NOT NULL indicates that the field cannot be NULL. This constraint requires that you also specify a DEFAULT value. Order is unimportant for these constraints. For example:

id INTEGER NOT NULL DEFAULT -1,
description STRING DEFAULT "NONE" NOT NULL

Table Creation Examples

The following are provided to illustrate the concepts described above.

CREATE TABLE users (
  id INTEGER,
  firstName STRING,
  lastName STRING,
  age INTEGER,
  PRIMARY KEY (id),
  COMMENT "This comment applies to the table itself"
)

CREATE TABLE usersNoId (
  firstName STRING,
  lastName STRING COMMENT "This comment applies to this field only",
  age INTEGER CHECK (age > 0 AND age < 150),
  ssn STRING NOT NULL DEFAULT "xxx-yy-zzzz",
  PRIMARY KEY (SHARD(lastName), firstName)
)

CREATE TABLE users.address (
  streetNumber INTEGER,
  streetName STRING,  // this comment is ignored by the DDL parser
  city STRING,
  /* this comment is ignored */
  zip INTEGER CHECK(zip > 11111 AND zip < 99999),
  addrType ENUM (home, work, other),
  PRIMARY KEY (addrType)
)

CREATE TABLE complex (
  COMMENT "this comment goes into the table metadata"
  id INTEGER, 
  PRIMARY KEY (id), # this comment is just syntax
  nestedMap MAP(RECORD( m MAP(FLOAT), a ARRAY(RECORD(age INTEGER)))),
  address RECORD (street INTEGER, streetName STRING, city STRING, \
                  zip INTEGER COMMENT "zip comment"),
  friends MAP (STRING),
  floatArray ARRAY (FLOAT),
  aFixedBinary BINARY(5),
  days ENUM(mon, tue, wed, thur, fri, sat, sun) NOT NULL DEFAULT tue
)

Prev	Up	Next
DDL Comments	Home	Modify Table Definitions