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

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

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

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

How to create two-way unique index or constraint