Drizzle TeamDT
Drizzle Team•10mo ago
Colum

Unique Index Constraints with Multiple Columns

I have the following constraint in libSQL:
uniqueIndex('notif_unique_constraint').on(
  table.userId,
  table.sourceUserId,
  sql`COALESCE(${table.postId}, '')`,
  sql`COALESCE(${table.replyId}, '')`,
  table.type,
),


This isn't parsed correctly by Drizzle but I have edited the migration to correct it:
CREATE UNIQUE INDEX `notif_unique_constraint` 
ON `notifications` (
  `user_id`, 
  `source_user_id`, 
  COALESCE(`post_id`, ''), 
  COALESCE(`reply_id`, ''), 
  `type`
);


The idea is to only create notifications if the relevant values of each particular action are unique.

Here's the relevant part of my insert function:
.onConflictDoNothing({
  target: [
    notificationSchema.userId,
    notificationSchema.sourceUserId,
    notificationSchema.postId,
    notificationSchema.replyId,
    notificationSchema.type,
  ],
})


But no matter what I try I always get the error:
"type": "LibsqlError",
      "message": "SQLITE_UNKNOWN: SQLite error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint: SQLite error: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint",


I have also tried using multiple unique indexes in the schema instead of the single one, in case it's an issue with coalescing null values:
uniqueIndex('notif_follow_unique')
  .on(table.userId, table.sourceUserId, table.type)
  .where(sql`${table.type} = 'FOLLOW'`),
uniqueIndex('notif_like_repost_unique')
  .on(table.userId, table.sourceUserId, table.postId, table.type)
  .where(sql`${table.type} IN ('LIKE', 'REPOST')`),
uniqueIndex('notif_reply_unique')
  .on(table.userId, table.sourceUserId, table.postId, table.replyId, table.type)
  .where(sql`${table.type} = 'REPLY'`),


But I get the same error. All three of these constraints are run, instead of the relevant one. onConflictDoNothing does have a where: option but I tried many ways of using this to narrow down the type and can't get it to work, even using raw sql.
Was this page helpful?