multi-multi join/bridge table, table creation questions

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

multi-multi join/bridge table, table creation questions

dboland9
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?

>> 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?

Finally, is there a best way to create a number of tables when an app. is first run?  For example, I can have one large function that contains all the SQL to create 6 tables and all the Python to create a connection, cursor, and execute the SQL.  Alternatively, I can have functions that pass either the db path, or the connection, to specialized functions that create just one table.  What is the pro/con of each?

Thanks!
Dave

Sent with [ProtonMail](https://protonmail.com) Secure Email.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Fw: multi-multi join/bridge table, table creation questions

dboland9
Sent with [ProtonMail](https://protonmail.com) Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Wednesday, August 14, 2019 8:54 AM, dboland9 <[hidden email]> 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?
>
>>> 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?
>
> Finally, is there a best way to create a number of tables when an app. is first run?  For example, I can have one large function that contains all the SQL to create 6 tables and all the Python to create a connection, cursor, and execute the SQL.  Alternatively, I can have functions that pass either the db path, or the connection, to specialized functions that create just one table.  What is the pro/con of each?
>
> Thanks!
> Dave
>
> Sent with [ProtonMail](https://protonmail.com) Secure Email.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: multi-multi join/bridge table, table creation questions

Igor Tandetnik-2
In reply to this post by dboland9
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users