I'm a bit disappointed:

sqlite> PRAGMA foreign_keys = ON;
sqlite> CREATE TABLE parent(a INTEGER PRIMARY KEY AUTOINCREMENT, b, c, UNIQUE (a, b));
sqlite> CREATE TABLE child(d INTEGER PRIMARY KEY AUTOINCREMENT, e, f, g, FOREIGN KEY (e, f) REFERENCES parent(a, b));
sqlite> INSERT INTO parent (b, c) VALUES (1, 2) RETURNING a;
1
sqlite> INSERT INTO child (e, f, g) VALUES (1, 2, 3);
Error: FOREIGN KEY constraint failed
sqlite> CREATE UNIQUE INDEX i ON parent(a, b);
sqlite> INSERT INTO child (e, f, g) VALUES (1, 2, 3);
Error: FOREIGN KEY constraint failed

It's mentioned that parent and child keys must have the same cardinality in foreign key documentation although with all the examples I still kinda hoped that as I can include a unique constraint on a composition of primary key and column I can make it work. I've tried adding an index as well.

Of course, I could have created a composite primary key in the parent, but then I wouldn't be able to use AUTOINCREMENT.

So I guess I just need assurance that this is and will be impossible by design.

• AUTOINCREMENT is irrelevant here. INTEGER PRIMARY KEY is enough to guarantee automatically generated values. From https://www.sqlite.org/autoinc.html:

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

• Your inserts are failing because there is no row (a, b) with values (1, 2). Change it to INSERT INTO child (e, f, g) VALUES (1, 1, 3); and it works.

I think your disappointment is premature and unnecessary.

The words about cardinality are only stating the obvious, which is that in the construct, "FOREIGN KEY ( fk-list ) REFERENCES ( ref-list )", both lists must have the same length. It would be entirely strange and useless to write SQL that did not conform to that requirement, and it would be an unusual mind that would or could assign meaning to the result of DDL that did not conform.

I suggest you read about AUTOINCREMENT in SQLite, particularly the 3rd paragraph of section 3. If you are still fond of writing keyword that into your DDL afterward, we can discuss how to accomplish your desired result, (assuming we can defocus on any specific "hows" you now favor.)

Usually, in discussion of foreign/primary key relationships, the term "cardinality" refers to something much different than those keys having to be comparable. I hope that usage shift is not confounding your perception of what solutions are open to you with SQLite.

What puzzles me about your post is: If your tables each have a primary key, which is necessarily unique in its table, then why are you messing around with compound keys at all? That is normally only done when there is no single primary key column. I wonder if you have overlooked that the primary key can be compound. Or maybe you saw that, but your love of AUTOINCREMENT has taken it off the table (so to speak.)

• AUTOINCREMENT is irrelevant here. INTEGER PRIMARY KEY is enough to guarantee automatically generated values. From https://www.sqlite.org/autoinc.html:

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

I'm aware. But:

the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

And that's what I wanted in this case.

• Your inserts are failing because there is no row (a, b) with values (1, 2). Change it to INSERT INTO child (e, f, g) VALUES (1, 1, 3); and it works.

Aw, thank you! It seems I've just needed a second pair of eyes. And the reason was I've switched to working on a semi equivalent example instead of the original point of the problem and made errors in translation.

Now that I know that FOREIGN KEY just works, I've found the source of the original problem:

sqlite> PRAGMA foreign_keys = ON;
sqlite> BEGIN;
CREATE TABLE parent (a INTEGER PRIMARY KEY, b, c, UNIQUE (a, b));
CREATE TABLE child (d INTEGER PRIMARY KEY, e, f, FOREIGN KEY (d, e) REFERENCES parent(b, a));
INSERT INTO parent (b) VALUES ('987654321') RETURNING a;
INSERT INTO child (d, e) VALUES ('987654321', 1);
1
Error: FOREIGN KEY constraint failed
sqlite> ROLLBACK;
sqlite> CREATE TABLE parent (a INTEGER PRIMARY KEY, b INTEGER, c, UNIQUE (a, b));
CREATE TABLE child (d INTEGER PRIMARY KEY, e, f, FOREIGN KEY (d, e) REFERENCES parent(b, a));
INSERT INTO parent (b) VALUES ('987654321') RETURNING a;
INSERT INTO child (d, e) VALUES ('987654321', 1);
1

The longer number was in fact sent quoted from Python as it was a str, so I ended up with a string in parent but due to INTEGER PRIMARY KEY in child, I've got number there. Adding affinity to b in parent fixed things.