Guideliness to manage unicity in Oracle NoSQL

[dario-vega]

Concepts

Oracle NoSQL database is designed for high performance and is maniacally focused on predictable, low latency. In an Oracle NoSQL Database, a transaction is a logical, atomic unit of work that entails one database access operation. Every data operation takes place in a single transaction, managed by the system. Oracle NoSQL Database does not implement an operation that needs transaction support across shards, but our end-users can adopt alternative solutions when designing applications. How to manage unicity?

Primary key

Primary keys and shard keys are important elements in your schema and help you access and distribute data efficiently.

You must designate one or more primary key columns when you create your table. The primary key cannot be changed and exists for the life of the table. If you need to update one of the fields in the key, you need to find an alternate key or use an entity.

Learn more

With Unique Keys Per Row

Secondary Index cannot be unique because it implies a transaction across shards, and as we already discussed, it is not currently supported. However, if you have arrays or are using JSON fields, you can guarantee uniqueness inside a row by using the clause WITH UNIQUE KEYS PER ROW.

If the optional WITH UNIQUE KEYS PER ROW clause is used, then there will not be any duplicates among the index keys generated from a row. This property applies to multikey indexes only and is also used in optimizing queries that perform unnesting.

In the following example, we can guarantee unicity for the field info.phones[].number. Keep in mind that 2 distinct rows can have the same phone number. But for the same row (user in this case), the database raises an error if you try to add the same phone number.

CREATE INDEX idx_phones ON UserInfo
(info.phones[].number AS INTEGER)
WITH UNIQUE KEYS PER ROW

If the optional WITH NO NULLS clause is specified in the CREATE INDEX statement, then the rows with NULL and/or EMPTY values on the indexed fields will not be indexed. But you can insert NULL values.

Unique index

There will be cases where you will want to use a secondary index to support some of your read requirements. In Oracle NoSQL, secondary index partitions live on the same shard as the primary data, so the updates to the secondary index are limited on a per-shard basis. Index updates in Oracle NoSQL are also atomic, so your application can be guaranteed that updates to records in the shard are consistent with updates to the secondary index, and these structures will never be out of sync.

But as discussed, we cannot create a unique index. If you need to update the column, you cannot use the column as a primary key. In this case, I recommend creating the secondary index for your read requirements and managing the duplicates in your application.

e.g., creating a dummy table with the columns to index as a primary key. Then you can execute multiple operations.

1. Insert a new line before updating the column using the option IF_ABSENT or the INSERT command
2. Only if the previous operation is successful, update the value in the main table.
3. You can delete the current value if needed

Keep connected. Things could change.