How to make columns within a row unique

Suppose that I have a Room entity in a postgresql database which consists of

Room {
  id:uuid, //PK of Table
  participant1: uuid, //foreign key to a user entity
  participant2: uuid  //foreign key to a user entity

I want to ensure that the in a record the participant1 and participant2 values should not be same.

1 answer

  • answered 2022-05-04 11:06 a_horse_with_no_name

    Use a check constraint:

    create table room
      id integer primary key, 
      participant1 uuid, 
      participant2 uuid, 
      constraint unique_participant 
        check (participant1 <> participant2)

    The above would allow NULL values in any of the columns or both.

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum