-- for some primary keys and unique keys
CREATE TABLE "0010_pk_ai" (
    "id" INTEGER NOT NULL PRIMARY KEY
);
CREATE TABLE "0020_composite_pks" (
  "foo_id" INTEGER NOT NULL,
  "bar_id"  INTEGER NOT NULL,
  PRIMARY KEY ("foo_id", "bar_id")
);
CREATE TABLE "0030_uks" (
    "id" INTEGER NOT NULL PRIMARY KEY,
    "username" TEXT NOT NULL UNIQUE,
    "email" TEXT NOT NULL UNIQUE,
    "password" TEXT NOT NULL
);

-- for some types
CREATE TABLE "0100_types" (
    "integer" INTEGER NOT NULL,
    "real" REAL NOT NULL,
    "text" TEXT NOT NULL,
    "blob" BLOB NOT NULL
);

-- for some default values
CREATE TABLE "0200_default_values" (
    "integer" INTEGER NOT NULL DEFAULT 1,
    "string" TEXT NOT NULL DEFAULT "UNKNOWN"
);

-- for foreign keys
CREATE TABLE "0300_fk_parent" (
    "id" INTEGER NOT NULL PRIMARY KEY
);
CREATE TABLE "0310_fk_child" (
    "id" INTEGER NOT NULL PRIMARY KEY,
    "parent_id" INTEGER NOT NULL,
    FOREIGN KEY ("parent_id") REFERENCES "0300_fk_parent" ("id")
);
