DT
Drizzle Team•2w 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,
),
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`
);
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,
  ],
})
.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",
"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'`),
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.
0 Replies
No replies yetBe the first to reply to this messageJoin

Did you find this page helpful?