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

onConflictDoNothing

does have a

where:

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.

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

onConflictDoNothing

does have a

where:

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.

Unique Index Constraints with Multiple Columns

Unique Index Constraints with Multiple Columns

Similar Threads

Similar Threads

Similar Threads