SQLite foreign key examples
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:
- You need to know the identifiers of both the child and the dog in order toto insert into this table.
- 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 exampleINSERT INTO dog VALUES(NULL, 'spot');SELECT last_insert_rowid(); -- gives the id of spot, assume 4 for this exampleINSERT 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 moduleimport sqlite3# Create a connection and cursor to your databaseconn = sqlite3.connect('example.db')c = conn.cursor()# Insert bobbyc.execute("""INSERT INTO child VALUES(NULL, 'bobby')""")# The python module puts the last row id inserted into a variable on the cursorbobby_id = c.lastrowid# Insert spotc.execute("""INSERT INTO dog VALUES(NULL, 'spot')""")spot_id = c.lastrowid# Insert the mappingc.execute("""INSERT INTO child_dog VALUES(?, ?)""", (bobby_id, spot_id));# Commitconn.commit()conn.close()
you need to have three tables for this. This is an example of Many-to-Many
Relationship.
Child- ChildID (PK)- NameDog- DogID (PK)- DogNameChild_Dog- ChildID (FK) - DogID (FK)