SQLite constraint for making 2 columns different

Question

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?

Have you considered table-level CHECK constraint? Something like CHECK(userId1 <> userId2). See sqlitetutorial.net/sqlite-check-constraint — PM 77-1
– PM 77-1, Commented Dec 3, 2024 at 23:33

Zegarek · Accepted Answer · 2025-02-10 19:54:36Z

6

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

edited Feb 10 at 19:54

Zegarek

30.1k5 gold badges27 silver badges32 bronze badges

answered Dec 3, 2024 at 23:28

samhita

4,2512 gold badges12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLite constraint for making 2 columns different

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related