sql - SQLite constraint for making 2 columns different - Stack Overflow

admin管理员组

文章数量:1328003

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?

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?

• sql
• sqlite
• check-constraints

Share Improve this question Follow edited Feb 10 at 19:54 Zegarek 26.7k55 gold badges2424 silver badges3030 bronze badges asked Dec 3, 2024 at 23:17 Rares DimaRares Dima 1,76911 gold badge1919 silver badges4545 bronze badges 1

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

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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);

本文标签： sqlSQLite constraint for making 2 columns differentStack Overflow