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
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 isUsers
, define a child table namedMailingAddress
asUsers.MailingAddress
.parent-table
.child-table
.grandchild-table
– Specifies a child table of an existing table. You must also specify the parent table, for exampleUsers.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 asSales:Users.MailingAddress
, orSales: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
orfamiliarName
. 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)))
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.
-
GENERATED ALWAYS AS IDENTITY
-
GENERATED BY DEFAULT AS IDENTITY
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:
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 TABLE demoJSONMR(name STRING,
jsonWithCounter JSON(counter as INTEGER MR_COUNTER,
person.count as LONG MR_COUNTER),
PRIMARY KEY(name)) IN REGIONS FRA,LON;
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)
)