sqlsqlite

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