How to match client ids randomly?

Question

SQLite

Good day to all! I have a database with clients who have their own id, I need to match these clients so that with the value "search" they can find a pair for themselves and at the same time exclude cases that two clients simultaneously receive the id of the same person.

Answer 1

It can be done, for example, as follows.
Create a table for clients and populate it with data:

CREATE TABLE clients (id INTEGER NOT NULL);
INSERT INTO clients (id) VALUES (1), (2), (3), (4), (5);

Create a table that will store client groups:

CREATE TABLE clients_groups (
  first_client_id INTEGER NOT NULL UNIQUE, 
  second_client_id INTEGER NOT NULL UNIQUE,
  FOREIGN KEY (first_client_id) REFERENCES clients (id) ON DELETE CASCADE ON UPDATE NO ACTION,
  FOREIGN KEY (second_client_id) REFERENCES clients (id) ON DELETE CASCADE ON UPDATE NO ACTION,
  CHECK(first_client_id != second_client_id)
);

Now, to select the ID of a free client (i.e., which does not participate in any of the groups), you need to run the query:

SELECT id FROM clients c
WHERE 
  c.id NOT IN(SELECT first_client_id FROM clients_groups) AND 
  c.id NOT IN(SELECT second_client_id FROM clients_groups)
ORDER BY RANDOM()
LIMIT 1;

And accordingly add a new group of these two clients to the clients_groups table.