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?
You can implement CHECK constraints on the columns.
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);