DT
Drizzle Team•4d ago
Cambaru

error: multiple primary keys for table "event_item_price" are not allowed

When wanting to create a composite primary key in my PG table i get an error when doing a drizzle kit push: 
import { text, integer, timestamp, pgTable, primaryKey } from "drizzle-orm/pg-core";
export const metadata = {
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow().$onUpdate(() => new Date()),
};

export const eventItemPrice = pgTable(
  "event_item_price",
  {
    eventId: text("event_id")
      .notNull()
      .references(() => event.id, { onDelete: "cascade" }),
    itemId: text("item_id")
      .notNull()
      .references(() => item.id, { onDelete: "cascade" }),
    price: integer("price"),
    token_price: integer("token_price"),
    ...metadata,
  },
  (table) => [
    primaryKey({ columns: [table.eventId, table.itemId] }),
  ]
);
import { text, integer, timestamp, pgTable, primaryKey } from "drizzle-orm/pg-core";
export const metadata = {
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow().$onUpdate(() => new Date()),
};

export const eventItemPrice = pgTable(
  "event_item_price",
  {
    eventId: text("event_id")
      .notNull()
      .references(() => event.id, { onDelete: "cascade" }),
    itemId: text("item_id")
      .notNull()
      .references(() => item.id, { onDelete: "cascade" }),
    price: integer("price"),
    token_price: integer("token_price"),
    ...metadata,
  },
  (table) => [
    primaryKey({ columns: [table.eventId, table.itemId] }),
  ]
);
error: multiple primary keys for table "event_item_price" are not allowed
  ..........
  length: 133,
  severity: 'ERROR',
  code: '42P16',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'index.c',
  line: '217',
  routine: 'index_check_primary_key'
}
error: multiple primary keys for table "event_item_price" are not allowed
  ..........
  length: 133,
  severity: 'ERROR',
  code: '42P16',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'index.c',
  line: '217',
  routine: 'index_check_primary_key'
}
3 Replies
Cambaru
CambaruOP•4d ago
It generates to this .sql: 
--> statement-breakpoint
CREATE TABLE "event_item_price" (
    "event_id" text NOT NULL,
    "item_id" text NOT NULL,
    "price" integer,
    "token_price" integer,
    "created_at" timestamp DEFAULT now(),
    "updated_at" timestamp DEFAULT now(),
    CONSTRAINT "event_item_price_event_id_item_id_pk" PRIMARY KEY("event_id","item_id")
);

ALTER TABLE "event_item_price" ADD CONSTRAINT "event_item_price_event_id_event_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."event"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "event_item_price" ADD CONSTRAINT "event_item_price_item_id_item_id_fk" FOREIGN KEY ("item_id") REFERENCES "public"."item"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
--> statement-breakpoint
CREATE TABLE "event_item_price" (
    "event_id" text NOT NULL,
    "item_id" text NOT NULL,
    "price" integer,
    "token_price" integer,
    "created_at" timestamp DEFAULT now(),
    "updated_at" timestamp DEFAULT now(),
    CONSTRAINT "event_item_price_event_id_item_id_pk" PRIMARY KEY("event_id","item_id")
);

ALTER TABLE "event_item_price" ADD CONSTRAINT "event_item_price_event_id_event_id_fk" FOREIGN KEY ("event_id") REFERENCES "public"."event"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "event_item_price" ADD CONSTRAINT "event_item_price_item_id_item_id_fk" FOREIGN KEY ("item_id") REFERENCES "public"."item"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
html_extraordinaire
html_extraordinaire•4d ago
Did you previously have a primary key there that you didn't remove or something? Try starting with a fresh DB and recreate your migrations unless this is a production DB or something. Or if you have access to the DB, inspect the table and see if there's an existing primary key then delete it.
Cambaru
CambaruOP•4d ago
I deleted the table on the database and now it worked! Thank you!

Did you find this page helpful?