Drizzle Team•2mo ago

[postgres] composite key does not result in constraint creation

i have this schema, and even though I've got a pretty basic composite primary key, whenever i go into a visual view of my db, no constraint is actually created. with other tables with single column pk's, it creates the constraint like ${tableName}_pkey, but in this case when i look on Neon there's no constraint here

export const preferenceTable = createTable(
  'preference',
  {
    userId: uuidv7('group_id').notNull(),
    defaultGroupId: uuidv7('default_group_id'),
  },
  ({ userId, defaultGroupId }) => [
    {
      pk: primaryKey({ columns: [userId, defaultGroupId] }),
    },
  ],
);

export const preferenceTable = createTable(
  'preference',
  {
    userId: uuidv7('group_id').notNull(),
    defaultGroupId: uuidv7('default_group_id'),
  },
  ({ userId, defaultGroupId }) => [
    {
      pk: primaryKey({ columns: [userId, defaultGroupId] }),
    },
  ],
);

4 Replies

scape•2mo ago

I've tried to reproduce it and test it with others drizzle-kit versions down to 0.29.0. This is definitely a bug. Besides your schema, I've tried to reproduce the one, that is provided in the docs (https://orm.drizzle.team/docs/indexes-constraints#composite-primary-key) and it resulted not in the way as it's supposed to:

CREATE TABLE "book" (
    "id" serial PRIMARY KEY NOT NULL,
    "name" text
);
--> statement-breakpoint
CREATE TABLE "books_to_authors" (
    "author_id" integer,
    "book_id" integer
);
--> statement-breakpoint
CREATE TABLE "user" (
    "id" serial PRIMARY KEY NOT NULL,
    "name" text
);

CREATE TABLE "book" (
    "id" serial PRIMARY KEY NOT NULL,
    "name" text
);
--> statement-breakpoint
CREATE TABLE "books_to_authors" (
    "author_id" integer,
    "book_id" integer
);
--> statement-breakpoint
CREATE TABLE "user" (
    "id" serial PRIMARY KEY NOT NULL,
    "name" text
);

(no primary key constraint.) I will create an issue for that. Thank you for reporting. In your case, you can modify generated sql, if you use migrations:

CREATE TABLE "preference" (
    "group_id" uuid NOT NULL,
    "default_group_id" uuid,
        PRIMARY KEY("group_id","default_group_id")
);

CREATE TABLE "preference" (
    "group_id" uuid NOT NULL,
    "default_group_id" uuid,
        PRIMARY KEY("group_id","default_group_id")
);

or you can just run the following sql saying add me a pk constraint to my table:

ALTER TABLE "preference" ADD CONSTRAINT "preference_pk" PRIMARY KEY("group_id","default_group_id");

ALTER TABLE "preference" ADD CONSTRAINT "preference_pk" PRIMARY KEY("group_id","default_group_id");

scape•2mo ago

Hmmm, I just went to github issues to see if someone already reported it. https://github.com/drizzle-team/drizzle-orm/issues/3805 In the comments, xorraxraxret suggests providing pk's via array, not via record. I've tried it on your table:

export const preferenceTable = pgTable(
  "preference",
  {
    userId: uuidv7("group_id").notNull(),
    defaultGroupId: uuidv7("default_group_id"),
  },
  ({ userId, defaultGroupId }) => [
    primaryKey({ columns: [userId, defaultGroupId] }),
  ],
);

export const preferenceTable = pgTable(
  "preference",
  {
    userId: uuidv7("group_id").notNull(),
    defaultGroupId: uuidv7("default_group_id"),
  },
  ({ userId, defaultGroupId }) => [
    primaryKey({ columns: [userId, defaultGroupId] }),
  ],
);

and it generated right schema:

CREATE TABLE "preference" (
    "group_id" uuid NOT NULL,
    "default_group_id" uuid,
    CONSTRAINT "preference_group_id_default_group_id_pk" PRIMARY KEY("group_id","default_group_id")
);

CREATE TABLE "preference" (
    "group_id" uuid NOT NULL,
    "default_group_id" uuid,
    CONSTRAINT "preference_group_id_default_group_id_pk" PRIMARY KEY("group_id","default_group_id")
);

So you can try doing this. Anyway, this is still a bug. Docs mention that you can provide a record of constrains, but this doesn't work

GitHub

[BUG]: Composite key ignored when generating migration · Issue #380...

Report hasn't been filed before. I have verified that the bug I'm about to report hasn't been filed before. What version of drizzle-orm are you using? 0.38.2 What version of drizzle-kit...

jackOP•2mo ago

Cool thanks for the thorough repro and looking into it I’ll do the aforementioned solution, but yea I agree the docs make it seem like my schema should work

Optio1•4w ago

I also agree, I hit this same issue. Definitely feels like a bug considering code verbatim from the docs does not work. Scape's fix above does work, simply removing the object inside of the array and passing the primaryKey straight through the array. The github issue linked above was closed and it is now being tracked here. https://github.com/drizzle-team/drizzle-orm/issues/3596

GitHub

[BUG]:Composite primary key not added to postgres schema when using...

Report hasn't been filed before. I have verified that the bug I'm about to report hasn't been filed before. What version of drizzle-orm are you using? 0.36.3 What version of drizzle-kit...

Gaming

Programming

[postgres] composite key does not result in constraint creation

Did you find this page helpful?