How to make columns within a row unique-CodePudding

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.

CodePudding user response：

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.