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:

IF NOT EXISTS

Optional clause. If you use this clause and a table of the same name and definition already exists in the current namespace, the statement neither creates a new table, nor returns an error. No action occurs.

If you do not use IF NOT EXISTS, and a table of the same name and definition already exists in the current namespace, the statement attempts to create the table, and fails. You cannot have two tables with the same name in one namespace.
table-name
Required. Specifies the full table name, regardless of where it exists in the table hierarchy. A table can be a top-level table created in the default namespace (sysdefault), a table in a non-default namespace, or a child or grandchild table of a parent table. Specify a fully-qualified table name as follows:
- parent-table.child-table – Specifies a new child table of an existing parent. To create a child table, specify the parent table followed by a period (.) before the child name. For example, if the parent table is Users, define a child table named MailingAddress as Users.MailingAddress.
- parent-table.child-table.grandchild-table – Specifies a child table of an existing table. You must also specify the parent table, for example Users.MailingAddress.Zip.
- namespace-name:table-name – Identifies a new table in a specific namespace, either user-created or in the default namespace (sysdefault:). Use the namespace followed by a colon (:) as a prefix to the new table name. For a new child table of a table in a namespace, or any further generation, use a fully qualified table name such as Sales:Users.MailingAddress, or Sales:Users.MailingAddress.Zip.
COMMENT

Optional. Use a comment to provide a brief description of the table. The comment is not interpreted at runtime, but becomes part of the table's metadata.
field-definition

Required. 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

Required for every table. Identifies at least one field in the table that is the primary key. For information on primary keys, see Primary Keys.

Note:

If the primary key field is an INTEGER data type, you can apply a serialized size constraint to it. See Integer Serialized Constraints for details.

To optionally define a shard key, use the SHARD keyword within the primary key statement. For information on shard keys, see Shard Keys.

For example:
```
PRIMARY KEY (SHARD(id), lastName)
```
USING TTL

Optional. Defines a default time-to-live value for the table's rows. See USING TTL for information on this clause.
IN REGIONS
Optional. In case, the table being created is an MR Table, this parameter lists all the regions that the table should span. You must mention at least one remote region in this clause to create the table as an MR Table. For information on MR Tables, see Life Cycle of MR Tables in the Concepts Guide.

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, allowable ranges or default values. Ann IDENTITY field, to be created by a sequence generator, is also permissable. 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)
```
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.
JSON

A JSON-formatted string.
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)
```
Number

A numeric type capable of handling any type of number of any value or precision.
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.
TIMESTAMP(<precision>)

Represents a point in time as a date and, optionally, a time value.

Timestamp values have a precision (0 - 9) which represents the fractional seconds to be held by the timestamp. A value of 0 means that no fractional seconds are stored, 3 means that the timestamp stores milliseconds, and 9 means a precision of nanoseconds. When declaring a timestamp field, the precision is required.

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.

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:

GENERATED ALWAYS AS IDENTITY
GENERATED BY DEFAULT AS IDENTITY

These are the Sequence Generator attributes you can define:

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:

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:

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)
)