SQLite foreign key examples

Question

I am not an expert in sql / sqlite.. suppose we have two tables:

CREATE TABLE child (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
);

CREATE TABLE MyTableB(
  dog TEXT, 
  FOREIGN KEY(dogList) REFERENCES child(id)
);

how will the INSERT? is correct my createTable operations? I would like to have: a child can have more than one dog a dog can have more children

EDIT

What if I wanted all the children and for each child a list of dogs associated with that child?

Answer 1

Many-To-Many

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 Many-To-Many relationship. This requires three tables:

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)
};

How to Insert

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 mapping table) must happen after inserts into the child and dog tables. This is for two related reasons:

1. You need to know the identifiers of both the child and the dog in order to to insert into this table.
2. 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.

Here are some example SQL statements for insert:

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);

Inserting In Python

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.

# 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()