See the following database structure:

                        v---------------------------------------------------|
  v----------------------------|---------------------------|                |
 ---------------      ---- --------- ------      ---- --------- --------- ----- 
| id | username |    | id | user_id | tag  |    | id | user_id | message | tag |
 ---- ----------      ---- --------- ------      ---- --------- --------- ----- 
| 1  | User1    |    | 1  | 1       | tech |    | 1  | 1       | Test1   | 1   |
| 2  | User2    |    | 2  | 1       | news |    | 2  | 2       | Test2   | 1   |
 ---- ----------      ---- --------- ------      ---- --------- --------- ----- 
   users                    tags                       messages

tags.user_id and messages.user_id both reference users.id. messages.tag references tags.id.

Users have tags available (rows in tags where rows.user_id = users.id) and messages (rows in messages where messages.user_id = users.id).

The problem is that any tag can be "attached" to the message, instead of only tags that are owned by the user. So I need an extra restriction that ensures that the tag referenced in messages.tag not only exists (foreign key restriction), but is also owned by the same user as the message itself (messages.user_id = tags.user_id).

I have not found a way yet to achieve this restriction, which is why I'm asking help.

• python: 3.8.10
• sqlite3.version: 2.6.0
• sqlite3.sqlite_version: 3.31.1

CodePudding user response：

From the manual creating a composite FK in Sqlite3 looks like:

CREATE TABLE parent(a PRIMARY KEY, c, d, e, f);
CREATE UNIQUE INDEX i1 ON parent(c, d);

CREATE TABLE child3(j, k, FOREIGN KEY(j, k) REFERENCES parent(c, d));