there is no unique constraint matching given keys for referenced table "users"
i get this error when run migration
schema
PostgresError: there is no unique constraint matching given keys for referenced table "users"
code: "42830"
PostgresError: there is no unique constraint matching given keys for referenced table "users"
code: "42830"
import { pgTable, text, timestamp } from "drizzle-orm/pg-core";
import { cuid2 } from "drizzle-cuid2/postgres";
import { relations } from "drizzle-orm";
export const users = pgTable("users", {
id: cuid2("id").unique().defaultRandom().primaryKey(),
userId: text("user_id").unique().notNull(),
name: text("name").notNull(),
email: text("email").notNull(),
createdAt: timestamp("created_at", { mode: "date" }).defaultNow(),
updatedAt: timestamp("updated_at", { mode: "date", precision: 3 }).$onUpdate(
() => new Date()
),
});
export const notes = pgTable("notes", {
id: cuid2("id").defaultRandom().primaryKey(),
userId: text("user_id")
.references(() => users.userId, {
onDelete: "cascade",
})
.unique()
.notNull(),
title: text("title").notNull(),
content: text("content"),
color: text("color").default("bg-gray-200"),
createdAt: timestamp("created_at", { mode: "date" }).defaultNow(),
updatedAt: timestamp("updated_at", { mode: "date", precision: 3 }).$onUpdate(
() => new Date()
),
});
export const usersRelations = relations(users, ({ many }) => ({
notes: many(notes),
}));
export const notesRelations = relations(notes, ({ one }) => ({
user: one(users, {
fields: [notes.userId],
references: [users.userId],
}),
}));
import { pgTable, text, timestamp } from "drizzle-orm/pg-core";
import { cuid2 } from "drizzle-cuid2/postgres";
import { relations } from "drizzle-orm";
export const users = pgTable("users", {
id: cuid2("id").unique().defaultRandom().primaryKey(),
userId: text("user_id").unique().notNull(),
name: text("name").notNull(),
email: text("email").notNull(),
createdAt: timestamp("created_at", { mode: "date" }).defaultNow(),
updatedAt: timestamp("updated_at", { mode: "date", precision: 3 }).$onUpdate(
() => new Date()
),
});
export const notes = pgTable("notes", {
id: cuid2("id").defaultRandom().primaryKey(),
userId: text("user_id")
.references(() => users.userId, {
onDelete: "cascade",
})
.unique()
.notNull(),
title: text("title").notNull(),
content: text("content"),
color: text("color").default("bg-gray-200"),
createdAt: timestamp("created_at", { mode: "date" }).defaultNow(),
updatedAt: timestamp("updated_at", { mode: "date", precision: 3 }).$onUpdate(
() => new Date()
),
});
export const usersRelations = relations(users, ({ many }) => ({
notes: many(notes),
}));
export const notesRelations = relations(notes, ({ one }) => ({
user: one(users, {
fields: [notes.userId],
references: [users.userId],
}),
}));
2 Replies
migration file
CREATE TABLE IF NOT EXISTS "notes" (
"id" varchar(32) PRIMARY KEY NOT NULL,
"user_id" text NOT NULL,
"title" text NOT NULL,
"content" text,
"color" text DEFAULT 'bg-gray-200',
"created_at" timestamp DEFAULT now(),
"updated_at" timestamp (3),
CONSTRAINT "notes_user_id_unique" UNIQUE("user_id")
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "users" (
"id" varchar(32) PRIMARY KEY NOT NULL,
"user_id" text NOT NULL,
"name" text NOT NULL,
"email" text NOT NULL,
"created_at" timestamp DEFAULT now(),
"updated_at" timestamp (3),
CONSTRAINT "users_id_unique" UNIQUE("id"),
CONSTRAINT "users_user_id_unique" UNIQUE("user_id")
);
--> statement-breakpoint
DO $$ BEGIN
ALTER TABLE "notes" ADD CONSTRAINT "notes_user_id_users_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("user_id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
WHEN duplicate_object THEN null;
END $$;
CREATE TABLE IF NOT EXISTS "notes" (
"id" varchar(32) PRIMARY KEY NOT NULL,
"user_id" text NOT NULL,
"title" text NOT NULL,
"content" text,
"color" text DEFAULT 'bg-gray-200',
"created_at" timestamp DEFAULT now(),
"updated_at" timestamp (3),
CONSTRAINT "notes_user_id_unique" UNIQUE("user_id")
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "users" (
"id" varchar(32) PRIMARY KEY NOT NULL,
"user_id" text NOT NULL,
"name" text NOT NULL,
"email" text NOT NULL,
"created_at" timestamp DEFAULT now(),
"updated_at" timestamp (3),
CONSTRAINT "users_id_unique" UNIQUE("id"),
CONSTRAINT "users_user_id_unique" UNIQUE("user_id")
);
--> statement-breakpoint
DO $$ BEGIN
ALTER TABLE "notes" ADD CONSTRAINT "notes_user_id_users_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("user_id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
WHEN duplicate_object THEN null;
END $$;
Try to remove the “unique” on users id.
Since it is a primary key, it is unique by design