Create Database objects
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.
CREATE NAMESPACE
statement.CREATE NAMESPACE [IF NOT EXISTS] namespace_name
Note:
Namespace names starting withsys
are reserved. You cannot use the prefix
sys
for any namespaces.
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.
- 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.
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)
)
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)
)
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.
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 regionmy_region1
should be set as the local region before creating the table.