CREATE TABLE

To create a table definition, use a CREATE TABLE statement, as follows:

CREATE TABLE [IF NOT EXISTS] [namespace:]table-name
 [COMMENT "comment string"]
 (field-definition, field-definition-2 [,...]
    PRIMARY KEY (field-name, field-name-2 [,...] ),
) [USING TTL ttl]
  [IN REGIONS region-name,region-name-2 [,...]]

where:

Field Definitions

When defining a table, field definitions take the form:

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

where:

Supported Data Types

The following data types are supported for table fields:

Field Constraints

Field constraints define information about the field, such as whether the field can be NULL, or what a row’s default value should be. Not all data types support constraints, and individual data types do not support all possible constraints.

Integer Serialized Constraints

You can put a serialized size constraint on an INTEGER data type, provided the INTEGER is used for a primary key field. Doing this can reduce the size the keys in your store.

To do this, use (n) after the primary key field name, where n is the number of bytes allowed for the integer. The meaningful range for n is 1 - 4. For example:

create table myId (id integer, primary key(id(3)))

The number of bytes allowed defines how large the integer can be. The range is from negative to positive. Note: Specifying an integer constraint value for number of bytes on an IDENTITY field is not permitted.

Table 1 - Allowed Integer Values by Number of Bytes

Number of Bytes Allowed Integer Values
1 -63 to 63
2 -8191 to 8191
3 -1048575 to 1048575
4 -134217727 to 134217727
5 Any integer value

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"

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

IDENTITY

You can define one IDENTITY field per table. All IDENTITY fields must have a numeric type: INTEGER. LONG, or NUMBER. An IDENTITY field can optionally be a primary key.

There are two ways to define an IDENTITY field. You can optionally specify one or more Sequence Generator attributes for the Sequence Generator (SG) associated with the IDENTITY. These are the options:

These are the Sequence Generator attributes you can define:

Table 2 - Sequence Generator Attributes

Attribute Type Default Value and Description
StartWith Number Default: 1 The first value in the sequence. Zero (0) is not permitted.
IncrementBy Long Default: 1 The value to increment the current value, which can be a positive or a negative number. Specifying a negative number for IncrementBy decrements values from the StartWith value.
MinValue Number Default: The minimum value of the field data type. The lower bound of the IDENTITY values that the SG supplies.
MaxValue Number Default: The maximum value of the field data type. The upper bound of the IDENTITY values that the SG supplies. If you do not specify this attribute, SG uses the maximum value of the field data type.
Cache Long Default: 1000 The number of values stored in cache on the client to use for the next IDENTITY value. When the set of values is exhausted, the SG requests another set to store in the local cache (unless you specify the Cycle attribute).
Cycle | NoCycle Boolean Default: NoCycle Determines whether to reuse the set of stored values in cache. For example, if the cache stores 1024 integers for the IDENTITY column, and you specify Cycle, when the IDENTITY value reaches 1023, the next row value is 0001. If you do not specify Cycle, Oracle NoSQL Database guarantees that each IDENTITY value in the column is unique, but not necessarily sequential.

For example:

CREATE Table T (id INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 2 INCREMENT BY 2 MAXVALUE 200),
name STRING,
PRIMARY KEY (id));

CREATE Table T_DEFAULT (id LONG GENERATED BY DEFAULT AS IDENTITY
(START WITH 1 INCREMENT BY 1 CYCLE CACHE 200),
account_id INTEGER,
name STRING,
PRIMARY KEY (account_id));

UUID

You can define one UUID field per table. UUID is a subtype of the STRING data type. The UUID column can be defined as GENERATED BY DEFAULT. The system then automatically generates a value for the UUID column if you do not supply a value for it.

For example :

CREATE TABLE myTable (id STRING AS UUID,name STRING, PRIMARY KEY (id));

In the above example, the id column has no “GENERATED BY DEFAULT” defined, therefore, whenever you insert a new row, you need to explicitly specify a value for the id column.

MR_COUNTER

In a multi-region table, you can create an MR_COUNTER datatype. MR_COUNTER datatype ensures that though data modifications can happen simultaneously on different regions, the data can always be merged into a consistent state. This merge is performed automatically by MR_COUNTER datatype, without requiring any special conflict resolution code or user intervention. You can also use the MR_COUNTER data type in a schema-less JSON field.

Example 1 - PN Counter Data Type in a Multi-Region Table

In the below example, you create a PN counter data type in two regions DEN and LON.

CREATE Table myTable( name STRING,
                      count INTEGER AS MR_COUNTER,
                      PRIMARY KEY(name)) IN REGIONS DEN,LON;

In the above example, while inserting data into the table, the system initially inserts the default value (0) when you either give the “DEFAULT” keyword or skip the column name in the INSERT clause.

Example 2 - JSON MR_COUNTER Data Type in a Multi-Region Table

Create a JSON MR_COUNTER data type in a multi-region table.

CREATE TABLE demoJSONMR(name STRING,
jsonWithCounter JSON(counter as INTEGER MR_COUNTER,
                      person.count as LONG MR_COUNTER),
PRIMARY KEY(name)) IN REGIONS FRA,LON;

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

USING TTL

USING TTL is an optional statement that defines a default time-to-live value for a table’s rows. See Using Time to Live for information on TTL.

If specified, this statement must provide a ttl value, which is an integer greater than or equal to 0, followed by a space, followed by time unit designation which is either hours or days. For example:

USING TTL 5 days

If 0 is specified, then either days or hours can be used. A value of 0 causes table rows to have no expiration time. Note that 0 is the default if a default TTL has never been applied to a table schema. However, if you previously applied a default TTL to a table schema, and then want to turn it off, use 0 days or 0 hours.

USING TTL 0 days

Be aware that if you altering an existing table, you can not both add/drop a field and alter the default TTL value for the field using the same ALTER TABLE statement. These two operations must be performed using separate statements.

Table Creation Examples

The following are provided to illustrate the concepts described above.

CREATE TABLE users
COMMENT "This comment applies to the table itself" (
  id INTEGER,
  firstName STRING,
  lastName STRING,
  age INTEGER,
  PRIMARY KEY (id),
)
CREATE TABLE temporary
COMMENT "These rows expire after 3 days" (
  sku STRING,
  id STRING,
  price FLOAT,
  count INTEGER,
  PRIMARY KEY (sku),
) USING TTL 3 days
CREATE TABLE Users
COMMENT "This is an MR table"(
  id INTEGER,
  firstName STRING,
  lastName STRING,
  age INTEGER,
  primary key (id)
) IN REGIONS us_east, us_west;
CREATE TABLE usersNoId (
  firstName STRING,
  lastName STRING COMMENT "This comment applies to this field only",
  age INTEGER,
  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,
  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
)
CREATE TABLE myJSON (
    recordID INTEGER,
    jsonData JSON,
    PRIMARY KEY (recordID)
)