Johanne Comments - Answer Overflow

Johanne

•Created by hachoter on 1/3/2024 in #help

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

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(),

3 replies

DTDrizzle Team

•Created by hachoter on 1/3/2024 in #help

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

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.

3 replies

Gaming

Programming