Many-to-Many Relationship

Date: March 15th 2016
Last updated: March 15th 2016

A many to many relationship occurs when an object of one table is linked to many objects in a second table, and vice versa. In this situation another table is required which typically has two columns, both containing integers referencing the ID of both tables.

In this example I have added on to an existing database containing animals, patient and vet_visit. Here I add another table for pet owners. However, one owner can have multiple pets, and each pet can have multiple owners. This is a case for the many to many relationship.

Create owners table and insert some data
Note the addition of DELETE ON CASCADE to the call to REFERENCES. This links up the tables on deletion. If a record gets deleted in the owners table, it also gets deleted from the ownerpatient table.

import sqlite3

# connect
con = sqlite3.connect('gitbook.db')

# cursor
cur = con.cursor()

# data
owners = [('ray', ), ('suzy', ), ('richard', ),]

# execute
cur.execute('CREATE TABLE owner (ownerid INTEGER PRIMARY KEY\
             AUTOINCREMENT NOT NULL, ownername TEXT NOT NULL);')

# add owners to new table 
cur.executemany('INSERT INTO owner (ownername) \
                 VALUES (?);', owners)

# commit and close connection
con.commit()
con.close()

Create many to many relationship and add data

# connect
con = sqlite3.connect('gitbook.db')

# cursor
cur = con.cursor()

# data
ownerpatient = [(1, 1, ),\
                (1, 2, ),\
                (1, 3, ),\
                (2, 1, ),\
                (2, 3, ),\
                (3, 1, ),]

# create many to many table
cur.execute('CREATE TABLE ownerpatient(\
             patient_id INTEGER,\
             owner_id INTEGER,\
             \
             FOREIGN KEY(patient_id) REFERENCES patient(patientid) \
             ON DELETE CASCADE,\
             \
             FOREIGN KEY(owner_id) REFERENCES owner(ownerid) \
             ON DELETE CASCADE)');

# add data to the many to many table 
cur.executemany('INSERT INTO ownerpatient (owner_id, patient_id)\
                 VALUES (?, ?);', ownerpatient)

# commit and close connection
con.commit()
con.close()

GUI