how should define a PRIMARY KEY in PartiQL at Amazon QLDB?

Question

I want to define a primary key in QLDB that the query language is PartiQL, how can I define ID as a primary key?

CREATE TABLE CarInfo WITH (aws_tags = `{ 'ID': INT, 'Manufacturer': STRING, 'ProductionYear': year, 'Model':STRING, 'VIN':STRING, 'Owner': STRING}`);

P.S., Since in the document of "Amazon QLDB-Developer Guide" is written "QLDB supports open content and does not enforce schema, so you don't define attributes or data types when creating tables.", maybe there is no PRIMARY KEY exsit in the PartiQL?

Answer 1

your initial assertion is correct -- there is no traditional primary key concept in QLDB at this time. to uniquely identify documents, you can use the document id. to enforce uniqueness, the current recommendation is to SELECT before INSERT in a transaction to verify that the data being inserted is unique. you'll also want to make sure that the fields you're using to enforce uniqueness/idempotency contain high-cardinality values, so that indexed queries will be optimized (see more on optimizing here).

regarding your comment about sorting, the content within a QLDB index is not sorted and there is currently no support for sorted indexes. as such, you would need to perform application-level sorting. if there are too many rows to feasibly do this, you may consider streaming to a secondary database for sorting and any other analytical queries.