Notes concernant l'activation des contraintes sur clé étrangère (FK) sur les bases de type SQLite3 utilisées en backend via Flask-SQLAlchemy.

Une contrainte de type FK¹⁾ est définie dans notre modèle mais ne semble pas s'appliquer. Ci-dessous la définition ayant présenté la problématique :

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase, mapped_column, relationship
from sqlalchemy import Integer, String, Text, ForeignKey
 
class Base(DeclarativeBase):
  pass
 
db = SQLAlchemy(model_class=Base)
 
class Category(db.Model):
    __tablename__ = "categories"
 
    id = mapped_column(Integer, primary_key=True,)
    label = mapped_column(String(50), nullable=False)
    description = mapped_column(Text, nullable=True)
    parent_id = mapped_column(Integer, ForeignKey("categories.id"), nullable=True,)
 
    children = relationship("Category", back_populates="parent",
                            cascade="all, delete-orphan", uselist=True)
    parent = relationship("Category", back_populates="children", remote_side='Category.id')
 
    def __init__(self, label):
        """Constructeur """
        self.label = label

La classe Category utilise un modèle(pattern) bien connu dit en “liste adjacente” (adjacency list pattern) qui se présente comme une table se référençant elle même (ici via la création d'une Foreign Key sur le champ parent_id ligne 16).

Ce modèle permet de représenter des structures hiérarchiques dans des tables et dans ce cas de créer des sous catégories dans des catégories. Pour une sous catégorie, le champ parent_id contient l'identifiant de la catégorie parente et une catégorie racine aura une valeur nulle dans son champ parent_id.

Lorsqu'on teste cet objet via la commande flask shell on se rend compte que la table est correctement créée avec la contrainte souhaitée mais que cette contrainte ne semble pas s'appliquer car il est possible de créer un sous-catégorie avec un parent_id aléatoire ne correspondant à aucun identifiant existant dans la table(relation) categories.

# Ce comportement est correct
 
# Ce comportement est anormal

via le client en ligne de commande, on peut afficher le contenu de notre fichier base de données :

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
 
def create_app(config: str=None):
    app = Flask(__name__, instance_relative_config=True)
    if config is None:
        app.config.from_pyfile('dev.py')
    else:
        logger.debug('Using %s as configuration', config)
        app.config.from_pyfile(config)
 
    db.init_app(app)
 
    # Ensure FOREIGN KEY for sqlite3
    if 'sqlite' in app.config['SQLALCHEMY_DATABASE_URI']:
        def _fk_pragma_on_connect(dbapi_con, con_record):  # noqa
            dbapi_con.execute('pragma foreign_keys=ON')
 
        with app.app_context():
            from sqlalchemy import event
            event.listen(db.engine, 'connect', _fk_pragma_on_connect)

• Imposer les contraintes FK pour les bases SQLite avec Flask-SQLAlchemy