Drizzle Team•11mo ago

PostgresError: there is no unique constraint matching given keys for referenced table "table_name"

I am getting this error, I looked at the migration file and i cannot see anything that would cause this error, I am not referencing the table table_name anywhere other than creating foreign key constraints to the id of this table which exists so I am not sure how it would cause any errors

1 Reply

Johanne•9mo ago

I get the exact same error, using:

"drizzle-orm" "^0.29.4",
"drizzle-kit": "^0.20.14",

"drizzle-orm" "^0.29.4",
"drizzle-kit": "^0.20.14",

My tables are as follows:

export const buildings = pgTable("buildings", {
  id: uuid("id").notNull().defaultRandom().unique(),
  name: text("name").notNull(),
  createdAt: timestamp("created_at").notNull(),
  updatedAt: timestamp("updated_at"),
});

export const devices = pgTable("devices", {
  id: uuid("id").notNull().defaultRandom().unique(),
  description: text("description").notNull(),
  zoneId: uuid("zoneId").references(() => zones.id, {
    onDelete: "cascade",
  }),
  createdAt: timestamp("created_at").notNull(),
  updatedAt: timestamp("updated_at"),
});

export const zones = pgTable("zones", {
  id: uuid("id").notNull().defaultRandom().unique(),
  name: text("name").notNull(),
  buildingId: uuid("buildingId").references(() => buildings.id, {
    onDelete: "cascade",
  }),
  createdAt: timestamp("created_at").notNull(),
  updatedAt: timestamp("updated_at"),
});

export const buildings = pgTable("buildings", {
  id: uuid("id").notNull().defaultRandom().unique(),
  name: text("name").notNull(),
  createdAt: timestamp("created_at").notNull(),
  updatedAt: timestamp("updated_at"),
});

export const devices = pgTable("devices", {
  id: uuid("id").notNull().defaultRandom().unique(),
  description: text("description").notNull(),
  zoneId: uuid("zoneId").references(() => zones.id, {
    onDelete: "cascade",
  }),
  createdAt: timestamp("created_at").notNull(),
  updatedAt: timestamp("updated_at"),
});

export const zones = pgTable("zones", {
  id: uuid("id").notNull().defaultRandom().unique(),
  name: text("name").notNull(),
  buildingId: uuid("buildingId").references(() => buildings.id, {
    onDelete: "cascade",
  }),
  createdAt: timestamp("created_at").notNull(),
  updatedAt: timestamp("updated_at"),
});

And the error I get: And the error I get:

severity_local: 'ERROR',
[2024-03-01T11:36:14.377Z]   severity: 'ERROR',
[2024-03-01T11:36:14.377Z]   code: '42830',
[2024-03-01T11:36:14.377Z]   where: 'SQL statement "ALTER TABLE "devices" ADD CONSTRAINT "devices_zoneId_zones_id_fk" FOREIGN KEY ("zoneId") REFERENCES "zones"("id") ON DELETE cascade ON UPDATE no action"\n' +
[2024-03-01T11:36:14.377Z]     'PL/pgSQL function inline_code_block line 2 at SQL statement',
[2024-03-01T11:36:14.377Z]   file: 'tablecmds.c',
[2024-03-01T11:36:14.377Z]   line: '11577',
[2024-03-01T11:36:14.377Z]   routine: 'transformFkeyCheckAttrs'

severity_local: 'ERROR',
[2024-03-01T11:36:14.377Z]   severity: 'ERROR',
[2024-03-01T11:36:14.377Z]   code: '42830',
[2024-03-01T11:36:14.377Z]   where: 'SQL statement "ALTER TABLE "devices" ADD CONSTRAINT "devices_zoneId_zones_id_fk" FOREIGN KEY ("zoneId") REFERENCES "zones"("id") ON DELETE cascade ON UPDATE no action"\n' +
[2024-03-01T11:36:14.377Z]     'PL/pgSQL function inline_code_block line 2 at SQL statement',
[2024-03-01T11:36:14.377Z]   file: 'tablecmds.c',
[2024-03-01T11:36:14.377Z]   line: '11577',
[2024-03-01T11:36:14.377Z]   routine: 'transformFkeyCheckAttrs'

Seeing as drizzle creates the migration, I can't see what is wrong either. Nvm, I fixed it with .unique() right after the uuid("id") statement in each table:

id: uuid("id").unique().notNull().defaultRandom(),

id: uuid("id").unique().notNull().defaultRandom(),

Gaming

Programming

PostgresError: there is no unique constraint matching given keys for referenced table "table_name"