QLDB unique field

Question

I want to create a unique field in QLDB. I tried to use UNIQUE, but get:

unexpected token found, KEYWORD : unique; Unexpected token following CREATE

Answer 1

According to the QLDB PartiQL Reference,

Amazon QLDB does not support all PartiQL operations.

And the CREATE TABLE reference docs say,

QLDB supports open content and does not enforce schema, so you don't define attributes or data types when creating tables. [emphasis mine]

Therefore, the logical conclusion is that QLDB does not support UNIQUE constraints on any fields you define in your table.

This confirmed by the QLDB tutorial

An INSERT statement creates the initial revision of a document with a version number of zero. To uniquely identify each document, QLDB assigns a document ID as part of the metadata.

Important

Because QLDB does not enforce schema, you can insert the same document into a table multiple times. Each insert statement commits a separate document entry to the journal, and QLDB assigns each document a unique ID.

The only field that is guaranteed to be unique by QLDB is the documentId.

Edit:

You can enforce the uniqueness from your application. Here's how:

1. Start a transaction
2. Execute SELECT id FROM myTable By id WHERE myUniqueField = ?
3. Determine whether or not a record already exists for that value of myUniqueField
4. Insert the new document (if the value is unique) or return the existing document (if desired)
5. Commit the transaction. (Technically, this can be merged with the previous step.)

You can only create an index on an empty table, so you should probably preemptively create an index for this field rather than wait until you have a performance problem because QLDB is doing a full table scan.

Edit:

As of 2020-09-30, QLDB supports adding an index to a non-empty table.

Answer 2

As per Matthew Pope's answer, QLDB currently has no support for schema or unique indexes.

That said, it is very easy to implement this behavior in your application:

SELECT id FROM foo BY id WHERE attr = ? -- [1]
INSERT INTO foo ?                       -- [2]

In your application, you should assert that no results are found by the SELECT query (1) and only run the INSERT statement (2) once that assertion passes.

If a competing transaction concurrently passes the assertion (1), only one of the transactions will commit. The other transaction will fail under OCC.

As written, the above is a unique attribute, not a unique index. For performance reasons, you will likely desire the attribute be indexed such that the SELECT query (1) doesn't perform a table scan.

CREATE INDEX ON foo (attr)