DT
Drizzle Team•13mo ago
hachoter

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
Johanne•11mo 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(),

Did you find this page helpful?