Add primary key to container tables

[boris-kolpackov]

Currently, container tables don't have primary keys. For example:

#pragma db object
struct object
{
  #pragma db id
  std::string id;

  std::vector<std::string> container;
}

CREATE TABLE "object_container" (
  "object_id" TEXT NOT NULL,
  "index" BIGINT NOT NULL,
  "value" TEXT NOT NULL,
  CONSTRAINT "object_id_fk"
  FOREIGN KEY ("object_id")
  REFERENCES "object" ("id")
  ON DELETE CASCADE);

CREATE INDEX "object_container_object_id_i"
  ON "object_container" ("object_id");

CREATE INDEX "object_container_index_i"
  ON "object_container" ("index");

While there is no reason to add primary keys to such tables from the ODB's functionality POV, there could be other reasons. For example, in PG, only tables with primary keys can be used in multi-master replication.

While I don't believe we can have a primary key for all containers (think std::multimap, which could have duplicate rows), for indexed containers (like std::vector above) I believe the {object_id, index} combo is unique.

The next question is whether we should do this by default or only if explicitly requested with a command line option and/or pragma. One con of doing this by default is that a primary key will create an index that will not be used (by ODB). So maybe not by default.

There is also the question of schema migration.

[boris-kolpackov]

Note that we have the unordered pragma which gets rid of the index column.