SQLAlchemy expression language and SQLite's on delete cascade

Question

I have two related tables namely users and roles and the relationship between them is many-to-many, so another association table userroles also exists.

The userroles table keeps track of the associated users and roles rows using foreign keys. For the foreign keys the ondelete parameter is set to "CASCADE" to get rid of the association row if any of the associated elements are being deleted.

This is my setup:

import sqlalchemy as sa


engine = sa.create_engine("sqlite:///:memory:", echo=True)
metadata = sa.MetaData()


userroles = sa.Table(
    "userroles",
    metadata,
    sa.Column("user_id", sa.Integer, sa.ForeignKey("users.id", ondelete="CASCADE")),
    sa.Column("role_id", sa.Integer, sa.ForeignKey("roles.id", ondelete="CASCADE")),
)


users = sa.Table(
    "users",
    metadata,
    sa.Column("id", sa.Integer, primary_key=True),
    sa.Column("name", sa.String),
)


roles = sa.Table(
    "roles",
    metadata,
    sa.Column("id", sa.Integer, primary_key=True),
    sa.Column("name", sa.String),
)

metadata.create_all(engine)
conn = engine.connect()

conn.execute(users.insert().values(name="Joe"))
conn.execute(roles.insert().values(name="Admin"))
conn.execute(roles.insert().values(name="User"))
conn.execute(userroles.insert().values(user_id=1, role_id=1))

However when I delete the Admin role which has the unique id of 1, the associated row is not being deleted from the userroles table. Why?

What am I missing here?

possibly related: https://stackoverflow.com/q/3020197/6560549 — SuperShoot, Aug 30 '19 at 11:39

score 3 · Accepted Answer · answered Aug 30 '19 at 12:16

Okay it seems like you need to enforce foreign keys for sqlite. So based on this answer one should do:

from sqlalchemy import event
from sqlalchemy.engine import Engine
from sqlite3 import Connection as SQLite3Connection

@event.listens_for(Engine, "connect")
def _set_sqlite_pragma(dbapi_connection, connection_record):
    if isinstance(dbapi_connection, SQLite3Connection):
        cursor = dbapi_connection.cursor()
        cursor.execute("PRAGMA foreign_keys=ON;")
        cursor.close()

SQLAlchemy expression language and SQLite's on delete cascade

1 Answers1

Linked