Create Database objects

A database object is any defined object in a database that is used to store or reference data. You use a CREATE command to create a Database object. You can use a database object to hold and manipulate the data.

Creating a namespace

A namespace defines a group of tables, within which all of the table names must be uniquely identified. Namespaces permit you to do table privilege management as a group operation. You can grant authorization permissions to a namespace to determine who can access both the namespace and the tables within it. Namespaces permit tables with the same name to exist in your database store. To access such tables, you can use a fully qualified table name. A fully qualified table name is a table name preceded by its namespaces, followed with a colon (:), such as ns1:table1.

All tables are part of some namespace. There is a default Oracle NoSQL Database namespace, called sysdefault. All tables are assigned to the default sysdefault namespace, until or unless you create other namespaces, and create new tables within them. You can't change an existing table's namespace. Tables in sysdefault namespace do not require a fully qualified name and can work with just the table name.

You can add a new namespace by using the CREATE NAMESPACE statement.

CREATE NAMESPACE [IF NOT EXISTS] namespace_name

Note:

Namespace names starting with sys are reserved. You cannot use the prefix sys for any namespaces.

The following statement defines a namespace named ns1.

CREATE NAMESPACE IF NOT EXISTS ns1

Creating a table

The table is the basic structure to hold user data. You use the CREATE TABLE statement to create a new table in the Oracle NoSQL Database.

Guidelines for creating a table:

The table definition must include at least one field definition, and exactly one primary key definition.
The field definition specifies the name of the column, its data type, whether the column is nullable or not, an optional default value, whether or not the column is an IDENTITY column , and an optional comment. All fields ( other than the PRIMARY KEY) are nullable by default.
The syntax for the primary key specification (key_definition) specifies the primary key columns of the table as an ordered list of field names.
The Time-To-Live (TTL) value is used in computing the expiration time of a row. Expired rows are not included in query results and are eventually removed from the table automatically by Oracle NoSQL Database. If you specify a TTL value while creating the table, it applies as the default TTL for every row inserted into this table.
You specify the REGIONS clause if the table being created is a Multi-Region table. The REGIONS clause lists all the regions that the table should span.

Example 1: The following CREATE TABLE statement defines a BaggageInfo table that holds baggage information of passengers in an airline system.

CREATE TABLE BaggageInfo (
ticketNo LONG,
fullName STRING,
gender STRING,
contactPhone STRING,
confNo STRING,
bagInfo JSON,
PRIMARY KEY (ticketNo)
)

Example 2: The following CREATE TABLE statement defines a stream_acct table that holds data from a TV streaming application.

CREATE TABLE stream_acct(
acct_id INTEGER,
acct_data JSON, 
PRIMARY KEY(acct_id)
)

Example 3: The following CREATE TABLE statement defines a stream_acct_new table that holds data from a TV streaming application. The rows of the table expire in 2 days.

CREATE TABLE stream_acct_new(
acct_id INTEGER,
acct_data JSON, 
PRIMARY KEY(acct_id)) USING TTL 2 days

Creating a region

Oracle NoSQL Database supports Multi-Region Architecture in which you can create tables in multiple KVStores and Oracle NoSQL Database will automatically replicate inserts, updates, and deletes in a multi-directional fashion across all regions for which the table spans. Each KVStore cluster in a Multi-Region NoSQL Database setup is called a Region.

Example 1: The following CREATE REGION statement creates a remote region named my_region1.

CREATE REGION my_region1

In a Multi-Region Oracle NoSQL Database setup, you must define all the remote regions for each local region. For example, if there are three regions in a Multi-Region setup, you must define the other two regions from each participating region. You use the CREATE REGION statement to define remote regions in the Multi-Region Oracle NoSQL Database.

Example 2: Create a table in a region.

CREATE TABLE stream_acct_region(acct_id INTEGER,
acct_data JSON,
PRIMARY KEY(acct_id)) IN REGIONS my_region1

Note:

The region my_region1 should be set as the local region before creating the table.