StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<h1>Many-To-Many</h1> <p>In order to support a child having zero or more dogs and a dog belonging to zero or more children, your database table structure needs to support a <strong>Many-To-Many</strong> relationship. This requires three tables:</p> <pre><code>CREATE TABLE child ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT ); CREATE TABLE dog ( id INTEGER PRIMARY KEY AUTOINCREMENT, dog TEXT ); CREATE TABLE child_dog { child_id INTEGER, dog_id INTEGER, FOREIGN KEY(child_id) REFERENCES child(id), FOREIGN KEY(dog_id) REFERENCES dog(id) }; </code></pre> <h1>How to Insert</h1> <p>An insert into each of the three tables must be separate SQL statements, but can take place in the context of same transaction. Inserts into the child_dog table (known as the <strong>mapping table</strong>) must happen after inserts into the child and dog tables. This is for two related reasons:</p> <ol> <li>You need to know the identifiers of both the child and the dog in order to to insert into this table.</li> <li>Because of the foreign key constraint, an insert into the child_dog table would fail if the child and/or dog referenced do not exist in the database or transaction.</li> </ol> <p>Here are some example SQL statements for insert:</p> <pre><code>INSERT INTO child VALUES(NULL, 'bobby'); SELECT last_insert_rowid(); -- gives the id of bobby, assume 2 for this example INSERT INTO dog VALUES(NULL, 'spot'); SELECT last_insert_rowid(); -- gives the id of spot, assume 4 for this example INSERT INTO child_dog VALUES(2, 4); </code></pre> <h1>Inserting In Python</h1> <p>Although your question did not mention python, there is a python tag on this question so I'll assume you want to know how to do this in python. The sqlite3 module in python provides a nice little shortcut which saves you from having to run the 'last_insert_rowid()' function explicitly.</p> <pre class="lang-py prettyprint-override"><code># Import the sqlite3 module import sqlite3 # Create a connection and cursor to your database conn = sqlite3.connect('example.db') c = conn.cursor() # Insert bobby c.execute("""INSERT INTO child VALUES(NULL, 'bobby')""") # The python module puts the last row id inserted into a variable on the cursor bobby_id = c.lastrowid # Insert spot c.execute("""INSERT INTO dog VALUES(NULL, 'spot')""") spot_id = c.lastrowid # Insert the mapping c.execute("""INSERT INTO child_dog VALUES(?, ?)""", (bobby_id, spot_id)); # Commit conn.commit() conn.close() </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload