Drizzle TeamDT
Drizzle Team•2y ago
AuroPick

How to create two-way unique index or constraint

lets say I will have a matches table
export const matches = pgTable(
  'matches',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('userId')
      .references(() => users.id)
      .notNull(),
    swipedUserId: uuid('swiped_user_id')
      .references(() => users.id)
      .notNull(),
    type: swipeTypeEnum('type').notNull().default('normal'),
    createdAt: timestamp('created_at').notNull().defaultNow(),
    updatedAt: timestamp('updated_at').notNull().defaultNow(),
    deletedAt: timestamp('deleted_at')
  },
  (table) => ({
    uniqueUserIdSwipedUserId: unique().on(table.userId, table.swipedUserId),
    userIdIdx: index('user_id_idx').on(table.userId),
    swipedUserIdIdx: index('swiped_user_id_idx').on(table.swipedUserId)
  })
)


in this case user retries to match swipedUser. It wont work.

but I want to avoid data insert if swipedUser tries to insert match with userId

I found this on stackoverflow but don't know how to implement this in drizzle

create table friendz (
  from_id int,
  to_id int
);

create unique index ifriendz on friendz(greatest(from_id,to_id), least(from_id,to_id));


summary

if there is a data like this
userId: 1
swipedUserId: 2

and when to try insert
userId: 2
swipedUserId: 1

I should get error
Was this page helpful?