Preventing multiple insertions of a connection between two users (SQL / SQLite)-CodePudding

I am currently working on a project where I want to store a connection between two users and some additional information in a SQLite database. The problem is that I want to store this connection exactly once and not have multiple entries for this connection.

So if I have userA and userB and for example the time this connection was made (userA, userB, time), I don't want to be able to store the reverse (userB, userA, time) as well.

My current solution is to sort the users and combine them into a single string, which is then used to create a primary key (userAuserB, userA, userB, time), preventing re-insertion. But I don't think this is the right way to do it.

Is there a better solution for this task?
Thanks, Ypselon.

CodePudding user response：

If your version of SQLite is 3.31.0 you can define 2 generated columns in the table which will be the min and the max connection values of the columns and then you can define a unique constraint for them:

CREATE TABLE tablename (
  userA TEXT,
  userB TEXT,
  user1 GENERATED AS (MIN(userA, userB)),
  user2 GENERATED AS (MAX(userA, userB)),
  UNIQUE(user1, user2)
);

The generated columns are virtual, meaning that they are not stored in the table.

See the demo.