#ICYDK: Introduction
Oracle NoSQL Database introduces IDENTITY column to provide the ability of auto incrementing a value each time a row is inserted into the table. IDENTITY Column
A primary key for a row in Oracle NoSQL Database table must be unique. But, how can we ensure that the primary key is always unique? One way would be to use a formula to generate the primary key. This may work well but is complex and not foolproof.
The IDENTITY column feature can generate a unique numeric value in sequence. These are typically used for Primary Key columns and are termed as an Identity column in the relational database world.
With the 18.3 release, Oracle NoSQL Database tables can
* Include an Identity Column (Primary Key or Shard Key) to be associated with a Sequence Generator.
* Associate a Sequence Generator to any numeric/Integer/Long type column in the table that requires an auto increment value.
An IDENTITY column Sequence Generator can be created with several configurable attributes that offer more flexibility.
* START WITH. The first value in the sequence. Default is 1.
* INCREMENT BY. The next number to increment the current value in the sequence is generated by adding INCREMENT BY value. The increment value can be a positive number or negative number. When INCREMENT BY is a positive number, values ascend as the SG adds them. If INCREMENT BY is a negative number, values descend/ The SG decrements from the last value with each iteration. The Default is 1.
* MINVALUE. The lower bound of the sequence value.
* MAXVALUE. The upper bound of the sequence value.
* CACHE. Determines how many values are available for each client to use for assigning IDENTITY numbers. Default is 1000.
* CYCLE or NO CYCLE. Indicates whether the sequence generator will reuse the IDENTITY numbers. The default is NO CYCLE. Using CYCLE indicates that IDENTITY numbers cannot be unique after the first CYCLE phase.
Use Case – Customer 360
This use case is about a typical bank which has a growing customer base. The bank is interested in in understanding their customer behavior and perform targeted marketing. They have multiple systems which capture different aspects of customer information and want to collate all of them in one table as a JSON document. Having built an interface which builds one JSON document they now want to store them in Oracle NoSQL Database. The bank only needs to identify what should be their Primary Key in this table. Let us look at different table structures which can be created using the Sequence Generator which will aid in generating the Primary Key while the bank interface only bothers about building their JSON document.
* IDENTITY column on a Primary key
CREATE TABLE customer(ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MAXVALUE 10000), customerProfile JSON, PRIMARY KEY (ID));
* IDENTITY column on a Shard Key
CREATE TABLE customer(ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MAXVALUE 10000), SSN INTEGER, customerProfile JSON, PRIMARY KEY (shard(ID),SSN);
* Identity column on an integer type (not a Primary/Shard Key).
CREATE TABLE customer(ID STRING, customerProfile JSON, uniqueID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MAXVALUE 10000), PRIMARY KEY (ID));
* IDENTITY column on an Indexed column
CREATE TABLE customer (ID STRING, customerProfile JSON, uniqueId INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 10 MAXVALUE 10000), PRIMARY KEY (ID));
Create index uniqueIdIndex on customer(uniqueId);
* Alter Table to add an IDENTITY column
CREATE TABLE customer (ID STRING, customerProfile JSON, PRIMARY KEY (ID));
ALTER TABLE customer (ADD uniqueId INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MAXVALUE 10000));
* IDENTITY column on a Parent and Child table.
CREATE TABLE customer (ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MAXVALUE 10000), customerProfile JSON, PRIMARY KEY (ID));
CREATE TABLE customer.address (addressID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MAXVALUE 10000), customerAddress JSON, PRIMARY KEY (addressID)); Summary
* Sequence Generator can be on a column with a numeric data types, generates numbers sequentially whenever a new row is added into the database.
* The Sequence Generator is commonly used to generate primary keys.
* Sequence Generator can also be used for columns other than Primary Keys.
* More details, limitations can be found in the official documentation – Sequence Generator. http://bit.ly/2Rb9WcE