Unique Constraint
I'm slightly confused as to why my unique constraint doesn't work as expected.
I have the following schema:
And I'm upserting like:
How come I end up with what seems like duplicated entries where userId, externalEmail, dataroomId, folderId, fileId seems to be duplicated. What could I be doing wrong? As you can see in the image below, the userId is duplicated, externalEmail, dataroomId and fileId are all null and folderIds are the same as well. All of these 5 fields were part of my constrain
I have the following schema:
export const userPermissionsTable = pgTable(
"user_permissions",
{
permissionId: uuid("permission_id").primaryKey().defaultRandom(),
userId: integer("user_id").references(() => usersTable.userId),
externalEmail: varchar("external_email", { length: 255 }),
dataroomId: uuid("dataroom_id").references(() => dataroomsTable.dataroomId),
folderId: uuid("folder_id").references(() => foldersTable.folderId),
fileId: uuid("file_id").references(() => filesTable.fileId),
canView: boolean("can_view").default(false).notNull(),
canEdit: boolean("can_edit").default(false).notNull(),
},
(t) => ({
unq: unique().on(t.userId, t.externalEmail, t.dataroomId, t.folderId, t.fileId),
})
);And I'm upserting like:
[permissionEntry] = await db
.insert(userPermissionsTable)
.values(permissionData)
.onConflictDoUpdate({
target: [
userPermissionsTable.userId,
userPermissionsTable.externalEmail,
userPermissionsTable.dataroomId,
userPermissionsTable.folderId,
userPermissionsTable.fileId,
],
set: permissionData,
})
.returning();
permissionEntries.push(permissionEntry);How come I end up with what seems like duplicated entries where userId, externalEmail, dataroomId, folderId, fileId seems to be duplicated. What could I be doing wrong? As you can see in the image below, the userId is duplicated, externalEmail, dataroomId and fileId are all null and folderIds are the same as well. All of these 5 fields were part of my constrain
unq: unique().on(t.userId, t.externalEmail, t.dataroomId, t.folderId, t.fileId),
