Unique Constraint

I'm slightly confused as to why my unique constraint doesn't work as expected.

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),
  })
);

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);

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

unq: unique().on(t.userId, t.externalEmail, t.dataroomId, t.folderId, t.fileId),

Unique Constraint

I'm slightly confused as to why my unique constraint doesn't work as expected.

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),
  })
);

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);

[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);

unq: unique().on(t.userId, t.externalEmail, t.dataroomId, t.folderId, t.fileId),

unq: unique().on(t.userId, t.externalEmail, t.dataroomId, t.folderId, t.fileId),

Unique Constraint

Unique Constraint

Similar Threads

Similar Threads

Similar Threads