Colleagues, please tell me how to correctly describe such a case.

1. I have a table with a list of objects that will be worked with. I guess so:

   CREATE TABLE IF NOT EXISTS objs (
    name TEXT PRIMARY KEY,
    remark TEXT
   );
   

2. I have a list of users who can work with objects. Moreover, each user has his own combination of objects with which he is allowed to work.

   CREATE TABLE IF NOT EXISTS users (
    userid INTEGER PRIMARY KEY,
    remark TEXT,
    -- FOREIGN KEY (objs) REFERENCES ???
   );
   

   How can I describe a link to an 'objs' table with a one-to-multiple relationship?

Ogogon.

If more than one user can work with an obj, you are really describing a many-to-many relationship. A third table provides the ability to implement that relationship. First I'd like to clean up your two tables so the rows represent singular items (and "fix" the user table):

CREATE TABLE IF NOT EXISTS obj (
 obj_name TEXT PRIMARY KEY,
 remark TEXT
);
CREATE TABLE IF NOT EXISTS user (
 userid INTEGER PRIMARY KEY,
 name TEXT   // user name instead of remark?
);

Then the third table looks like this:

CREATE TABLE obj_user (
    obj_name TEXT REFERENCES obj(obj_name),
    userid INTEGER REFERENCES user(userid),
    PRIMARY KEY(obj_name, userid)
);

To populate the tables:

PRAGMA FOREIGN_KEYS=1;   /* enforce referential integrity */
INSERT INTO obj(obj_name,remark) VALUES('obj1','remark1');
INSERT INTO obj(obj_name,remark) VALUES('obj2','remark2');
INSERT INTO user(userid,name) VALUES(1,'Joe');
INSERT INTO user(userid,name) VALUES(2,'Bob');
INSERT INTO obj_user(obj_name,userid) VALUES('obj1',1);
INSERT INTO obj_user(obj_name,userid) VALUES('obj1',2);
INSERT INTO obj_user(obj_name,userid) VALUES('obj2',2);

Running this under sqlite3:

sqlite> SELECT obj_user.obj_name FROM obj_user WHERE userid=1;
obj1
sqlite> SELECT obj_user.obj_name FROM obj_user WHERE userid=2;
obj1
obj2

Alas, I get something different.

As a result, the structure of the base took on this form. The 'tab' and 'tabs' mentioned are completely other data structures not associated with the database. The database was created by a C application.

PRAGMA FOREIGN_KEYS=1;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS tabs;
DROP TABLE IF EXISTS usertab;
-- This is users
CREATE TABLE IF NOT EXISTS users (
    user TEXT PRIMARY KEY,
    comment TEXT
);
-- This is list of firewall tables
CREATE TABLE IF NOT EXISTS tabs (
    tab TEXT PRIMARY KEY,
    comment TEXT
);
CREATE TABLE usertab (
    user TEXT REFERENCES user(users),
    tab TEXT REFERENCES tab(tabs),
    PRIMARY KEY(user,tab)
);

When I try to make a record in the third table (usertab), I get the response "SQL error: no such table: main.tab". When I try to do the same thing from a console program, the answer is similar:

sqlite> PRAGMA table_info(users);
0|user|TEXT|0||1
1|comment|TEXT|0||0
sqlite> PRAGMA table_info(tabs);
0|tab|TEXT|0||1
1|comment|TEXT|0||0
sqlite> PRAGMA table_info(usertab);
0|user|TEXT|0||1
1|tab|TEXT|0||2
sqlite> SELECT * FROM users;
ogogon|Ogogon !!!
root|Super User
sqlite> SELECT * FROM tabs;
smtp|Access to SMTP and IMAP
ssh|Access to SSH
voip|Access to SIP and IAX
sqlite> SELECT * FROM usertab;
sqlite> PRAGMA FOREIGN_KEYS=1; INSERT INTO usertab(user,tab) VALUES('ogogon','ssh');
Parse error: no such table: main.tab
sqlite>

Thank you! It's working!

What should I do if I need to delete a 'tab' or 'user'? Do they need to be deleted from the 'usertab' table manually, with a separate query, or will the database itself delete records in this table when deleting objects to which links are installed?