How can "on delete restrict" be used in a model?

Question

I have figured out how to use "on delete cascade", but am unclear on how to do "on delete restrict" constraints. What I would like to achieve is to not be able to delete a parent that has a child or children records.

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    fullname = db.Column(db.String)
    password = db.Column(db.String)

    posts = db.relationship("Post", backref='user', cascade="all, delete, delete-orphan")

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String, nullable=False)
    description = db.Column(db.String, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id', onupdate="CASCADE", ondelete="CASCADE"))

CREATE TABLE posts (
    id INTEGER NOT NULL,
    title VARCHAR NOT NULL,
    description VARCHAR NOT NULL,
    user_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE users (
    id INTEGER NOT NULL,
    name VARCHAR,
    fullname VARCHAR,
    password VARCHAR,
    PRIMARY KEY (id)
);

Replacing "delete" with "restrict" just allows me to delete the parents and retain the orphaned rows.

How do I properly specify the "restrict" behavior?

score 7 · Accepted Answer · edited May 23 '17 at 12:22

SQLite does not support foreign key constraints by default. They must be enabled at compile time and enabled at run time, otherwise they are silently ignored.

You can check if foreign keys are enabled by running pragma foreign_keys in a sqlite shell. If it returns 1, they are enabled. If it returns 0, they are disabled. If it does not return anything, they are not supported and sqlite must be recompiled to support them.

If foreign keys are disabled, you can instruct SQLAlchemy to enable them when creating connections.

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

_{source: https://stackoverflow.com/a/15542046/400617}

SQL foreign key cascades are different than SQLAlchemy's relationship cascades (scroll down that second link to see a detailed comparison of the two). SQLAlchemy has no "restrict" cascade. You should specify that on the foreign key. Be sure to recreate/migrate the database if you change a foreign key that already exists.

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey(User.id, ondelete='RESTRICT'), nullable=False)

How can "on delete restrict" be used in a model?

1 Answers1