SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
PRAGMA foreign_keys = ON;
CREATE TABLE users (
  user_id INTEGER PRIMARY KEY, 
  user TEXT NOT NULL,
  number_of_items_allowed INTEGER NOT NULL CHECK(number_of_items_allowed >= 0)
);
CREATE TABLE items (
  item_id INTEGER PRIMARY KEY, 
  item TEXT NOT NULL
);
CREATE TABLE users_items (
  user_id INTEGER NOT NULL REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE, 
  item_id INTEGER NOT NULL REFERENCES items (item_id) ON UPDATE CASCADE ON DELETE CASCADE, 
  PRIMARY KEY(user_id, item_id)
);
CREATE TRIGGER check_number_before_insert_users_items 
BEFORE INSERT ON users_items
BEGIN
   SELECT
      CASE
	WHEN (SELECT COUNT(*) FROM users_items WHERE user_id = NEW.user_id) >=
	     (SELECT number_of_items_allowed FROM users WHERE user_id = NEW.user_id)
	  THEN RAISE (ABORT, 'No more items allowed')
      END;
END;
CREATE TRIGGER check_number_before_update_users 
BEFORE UPDATE ON users
BEGIN
   SELECT
      CASE
	WHEN (SELECT COUNT(*) FROM users_items WHERE user_id = NEW.user_id) > NEW.number_of_items_allowed
	  THEN RAISE (ABORT, 'There are already more items for this user than the value inserted')
      END;
END;
INSERT INTO users (user, number_of_items_allowed) VALUES
  ('user1', 3), ('user2', 2); 
INSERT INTO items (item) VALUES
  ('item1'), ('item2'), ('item3'), ('item4'), ('item5');
INSERT INTO users_items (user_id, item_id) VALUES
  (1, 1), (1, 2), (1, 3), 
  (2, 4), (2, 5); 
SELECT u.*, i.*
FROM users u
LEFT JOIN users_items ui ON ui.user_id = u.user_id 
LEFT JOIN items i ON i.item_id = ui.item_id 


user_id
user
number_of_items_allowed
item_id
item


1
user1
3
1
item1

1
user1
3
2
item2

1
user1
3
3
item3

2
user2
2
4
item4

2
user2
2
5
item5


SELECT u.*, GROUP_CONCAT(i.item) list_of_items
FROM users u
LEFT JOIN users_items ui ON ui.user_id = u.user_id 
LEFT JOIN items i ON i.item_id = ui.item_id
GROUP BY u.user_id


user_id
user
number_of_items_allowed
list_of_items


1
user1
3
item1,item2,item3

2
user2
2
item4,item5


INSERT INTO users_items (user_id, item_id) VALUES
  (1, 4);


user_id
user
number_of_items_allowed
list_of_items


1
user1
3
item1,item2,item3

2
user2
2
item4,item5


No more items allowed

UPDATE users
SET number_of_items_allowed = 1;


user_id
user
number_of_items_allowed
list_of_items


1
user1
3
item1,item2,item3

2
user2
2
item4,item5


There are already more items for this user than the value inserted
user_id	user	number_of_items_allowed	item_id	item
1	user1	3	1	item1
1	user1	3	2	item2
1	user1	3	3	item3
2	user2	2	4	item4
2	user2	2	5	item5