We have:

* a table `Room`
* a table `Person`
* an intermediary table `PersonRoom`

in `Room` i have a *maximum* of `Person` which corresponds to the maximum of persons that can go in the room.
Like this:

```
Room
  id
  maximum_persons
Person
  id
  name
PersonRoom
  id
  room_id
  person_id

```

Each time a person with "`my_person_id`" wants to go into a room with "`my_room_id`", I should do something like this:

```
INSERT INTO PersonRoom (room_id, person_id)
                VALUES (my_room_id, my_person_id)

```

But *I should not be able to insert a person if the room is full* ie I should do something like (I know that *this is not valid SQL*):

```
@TOTAL = SELECT COUNT(*) FROM PersonRoom
         WHERE room_id = my_room_id;
IF @TOTAL < (SELECT maximum_persons) FROM Room WHERE id = my_room_id:
    INSERT INTO PersonRoom (room_id, person_id)
                    VALUES (my_room_id, my_person_id)

```

I'd like to handle this on the Database side

* Is there a solution for SQLite? If not, is there a solution for PostgreSQL
* What would be the SQL?
* If I do this in **one transaction**, will it be 100% safe if there are many people who try to register at the same time for the same room?