How can you use "to restrict delete" in a model?
I figured out how to use "on delete cascade" but it is not clear how to do the "on delete cascade" constraints. What I would like to achieve is not to delete the parent with child or child entries.
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 "constraint" allows me to delete parents and keep the orphan axes.
How do I correctly specify the "restrict" behavior?
source to share
SQLite does not support foreign key constraints by default. They must be included at compile time and included at run time, otherwise they are silently ignored.
You can check if foreign keys are enabled by running pragma foreign_keys
in sqlite shell. If it returns 1, they are included. If it returns 0, they are disabled. If it doesn't return anything, they are not supported and sqlite needs to be recompiled to support them.
If foreign keys are disabled, you can tell 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: fooobar.com/questions/213591 / ...
The SQL foreign key cascades are different from the SQLAlchemy cascades relationship (scroll through this second link to see a detailed comparison of the two). SQLAlchemy does not have a "bounding" cascade. You must specify this for the foreign key. Don't forget to update / migrate your 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)
source to share