Primary Keys

Every table must have one or more fields designated as the primary key. This designation occurs at the time that the table is created, and cannot be changed after the fact. Oracle NoSQL Database supports the following types for the primary key: INTEGER, LONG, FLOAT, DOUBLE, NUMBER, STRING, ENUM, BOOLEAN, and TIMESTAMP.

A table's primary key uniquely identifies every row in the table. In the simplest case, it is used to retrieve a specific row so that it can be examined and/or modified.

For example, a table might have five fields: productName, productType, color, size, and inventoryCount. To retrieve individual rows from the table, it might be enough to just know the product's name. In this case, you would set the primary key field as productName and then retrieve rows based on the product name that you want to examine/manipulate.

The table statement you use to define this table is:

CREATE TABLE myProducts (
    productName STRING,
    productType STRING,
    color ENUM (blue,green,red),
    size ENUM (small,medium,large),
    inventoryCount INTEGER,
    // Define the primary key. Every table must have one.
    PRIMARY KEY (productName)
)

Note:

If the primary key field is an INTEGER data type, you can apply a serialized size constraint to it. See Integer Serialized Constraints.
If the primary key must represent an approximate numeric type, you have an option to choose between FLOAT and DOUBLE data types. When serialized as a primary key, the DOUBLE data type uses 10 bytes and stores up to 16 decimal places. The FLOAT data type uses 5 bytes and stores up to 8 decimal places. Therefore, DOUBLE data type is more suitable as it provides a higher precision and a wider range as compared to the FLOAT data type.
If the primary key must represent a string data type, you have an option to choose between STRING and ENUM. The ENUM data type mandates choosing from a list of permitted values. When used as a primary key, the cardinality is small. Also, any addition or deletion to the ENUM values requires schema evolution. The primary keys can only be changed by dropping and recreating the table. Therefore, changing the ENUM values of a primary key is an invasive operation. The STRING data type is better suited for primary keys as its value is a sequence of zero or more Unicode characters

Composite Keys

You can use multiple fields for your primary key, which is termed as a composite key.

For example, consider the following table statement:

CREATE TABLE myProducts (
    productName STRING,
    productType STRING,
    color ENUM (blue,green,red),
    size ENUM (small,medium,large),
    inventoryCount INTEGER,
    // Define the primary key. Every table must have one.
    PRIMARY KEY (productName, productType)
)

Here, the columns productName and productType are both declared as primary key fields.

The composite keys are useful in cases where the keys conjointly help in identifying a unique row.

See Reading Table Rows to retrieve multiple rows from your table.

See Using multiDelete() to delete multiple rows at a time.

Data Type Limitations

Fields can be designated as primary keys only if they are declared to be one of the following types:

Integer
Long
Number
Float
Double
String
Timestamp
Enum

Partial Primary Keys

Some of the methods you use to perform multi-row operations allow, or even require, a partial primary key. A partial primary key is, simply, a key where only some of the fields comprising the row's primary key are specified.

For example, the following example specifies three fields for the table's primary key:

CREATE TABLE myProducts (
    productName STRING,
    productType STRING,
    productClass STRING,
    color ENUM (blue,green,red),
    size ENUM (small,medium,large),
    inventoryCount INTEGER,
    // Define the primary key. Every table must have one.
    PRIMARY KEY (productName, productType, productClass)
)

In this case, a full primary key would be one where you provide value for all three primary key fields: productName, productType, and productClass. A partial primary key would be one where you provide values for only one or two of those fields.

Note that order matters when specifying a partial key. The partial key must be a subset of the full key, starting with the first field specified and then adding fields in order. So the following partial keys are valid:

productName
productName, productType

Shard Keys

Shard keys identify which primary key fields are meaningful in terms of shard storage. That is, rows that contain the same values for all the shard key fields are guaranteed to be stored on the same shard offering high-performance retrievals and horizontal scalability. This matters for some operations that promise atomicity of the results. (See Executing a Sequence of Operations for more information.)

For example, suppose you set the following primary keys:

PRIMARY KEY (productType, productName, productClass)

You can guarantee that rows are placed on the same shard using the values set for the productType and productName fields like this:

PRIMARY KEY (SHARD(productType, productName), productClass)

Note:

The order matters when it comes to the shard keys. The keys must be specified in the order that they are defined as primary keys, with no gaps in the key list. In other words, given the above example, it is impossible to set productType and productClass as shard keys without also specifying productName as a shard key.
The shard keys can't be declared in the create table statement of a non-root table (in cases of child tables). An error is returned in such scenarios.

For more details on table modeling and design using primary keys, see Choice of Keys in NoSQL Database.