On 8/14/2019 8:54 AM, dboland9 wrote:
I'm doing a Python app (not that the language really matters - just for
context) that uses SQLite to store data. I have a many-to-many (MTM)
relationship. After a lot of Googling and reading I have concluded that:
I need to create the join/bridge table just like all the other tables. In
other words, it will not be created automatically using some SQL that I have
yet to understand. True or false?
True. There is no special built-in syntax for many-to-many relationships (nor
for one-to-many relationships, for that matter). You just use tables that store
each other's keys.
In all the examples I have seen, the join/bridge table is populated (INSERT
INTO) manually - as in not done by SQL. Here is my problem. I will need to
insert the primary keys as foreign keys into the table. In the examples I have
seen, they knew what those number were (all 12 of them). I won't have that
situation as the primary keys from the other tables are AUTO_NUMBER. That
means I have to query those tables before I can INSERT INTO the join/bridge
tables. Too much work. Is there a better easier way? How about some complete
examples?
You may be looking for sqlite3_last_insert_rowid API function (
https://www.sqlite.org/c3ref/last_insert_rowid.html ) and/or
last_insert_rowid() SQL function (
https://www.sqlite.org/lang_corefunc.html#last_insert_rowid ).
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
