[SOLVED] SQLite constaint for making 2 columns different

SQLite constaint for making 2 columns different

I have 2 SQLite tables, users and transactions where a "transaction" happens between exactly 2 users. A short example would be:

CREATE TABLE users(
  id INTEGER PRIMARY KEY ASC
);

CREATE TABLE transactions(
  id INTEGER PRIMARY KEY ASC
  participant_1_id INTEGER REFERENCES users,
  participant_2_id INTEGER REFERENCES users,
);

Now, I want to enforce that participant_1_id and participant_2_id must be different because a user cannot make a transaction with themselves.

However everything I find on both SO and Google is only about making a pair of columns UNIQUE, which is of no use to me since user 1 and user 2 may perform multiple transactions between themselves, so the pair of (userId1, userId2) will 100% show up more than once in transactions.

Is there a way to enforce 2 columns to be different?

Solution

You can implement CHECK constraints on the columns.

Fiddle

CREATE TABLE users (
  id INTEGER PRIMARY KEY ASC
);

CREATE TABLE transactions (
  id INTEGER PRIMARY KEY ASC,
  participant_1_id INTEGER REFERENCES users(id),
  participant_2_id INTEGER REFERENCES users(id),
  CHECK (participant_1_id != participant_2_id)
);

This succeeds

INSERT INTO transactions (participant_1_id, participant_2_id) VALUES (1, 2);

This fails

INSERT INTO transactions (participant_1_id, participant_2_id) VALUES (1, 1);