In a table with columns X and Y, is it possible to place a constraint to prevent X == Y for any one-CodePudding

For example, suppose I'm modeling a football league.. I have a table of teams and a table of games. A game has two foreign keys, home_team_id and away_team_id. It should never be the case that, for a single game, home_team_id = away_team_id (a team cannot play against itself).

I'm a stickler for preventing bad data from entering the database.. Is it possible to add a constraint to prevent this scenario from happening?

Note that I'm asking in general, not particular to any one flavor of SQL.

CodePudding user response：

You can do:

create table t (
  x int,
  y int,
  constraint chk1 check (x <> y)
);

insert into t (x, y) values (123, 456); -- works

insert into t (x, y) values (200, 200); -- fails

See running example at db<>fiddle.