Skip to content
Learn Netverks
0

not null constraint failed

asked 16 hours ago by @qa-lrybrzeyufcjpuflddjj 0 rep · 95 views

python sqlalchemy

I program in python. I run the following method

def delete_category(id):
    # Récupérer la categorie à supprimer
    print("ca passe1")
    category = db.session.query(Category).filter_by(id=id).first()
    print(category)
    if category:
        print("ca passe2")
        # Supprimer la categorie
        db.session.delete(category)
        print("ca passe3")
        # Commit pour sauvegarder les changements
        db.session.commit()
        print("ca passe4")
        return jsonify({"message" : "La Categorie a été supprimée de la base de donnée."}), 200
    else:
        return jsonify({"error" : "Categorie non trouvée en base de donnée."}), 401

the result of print is the following

ca passe1

Category id=3, category=clavier, description=Clavier pour gaming

ca passe2

ca passe3

and it raises an error

       sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: products.category_id
E       [SQL: UPDATE products SET category_id=? WHERE products.id = ?]
E       [parameters: (None, 'prod003')]
E       (Background on this error at: https://sqlalche.me/e/20/gkpj)

here is the definition of classes

class Product(db.Model):
    __tablename__ = 'products'
    
    id = db.Column(db.String(10), primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id'), nullable=False)
    description = db.Column(db.Text)
    price = db.Column(db.Float, nullable=False)
    stock = db.Column(db.Integer, default=0)

    # Relation avec le produit
    category = db.relationship('Category', backref='products')

class Category(db.Model):
    __tablename__ = 'categories'
    
    id = db.Column(db.Integer, primary_key=True)
    category = db.Column(db.String(20), unique=True, nullable=False)
    description = db.Column(db.Text)

can you help me please

Comments on this question (0)

Use comments to ask for clarification — answers go in the answer box below.

Log in to comment on this question.

1 answer

3

Accepted answer

The error happens because when you delete a Category, SQLAlchemy's default behavior is to set the foreign key of all related Product records to NULL. However, your model defines category_id as nullable=False, creating a conflict.

To fix this, you must configure a delete cascade so that deleting a category automatically deletes its associated products.

Solution

Update the relationship in your Product model to use db.backref with cascade="all, delete-orphan":

class Product(db.Model):
    __tablename__ = 'products'
    
    id = db.Column(db.String(10), primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    
    # 1. (Optional but recommended) Add ON DELETE CASCADE at DB level
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id', ondelete='CASCADE'), nullable=False)
    
    description = db.Column(db.Text)
    price = db.Column(db.Float, nullable=False)
    stock = db.Column(db.Integer, default=0)

    # 2. Add the cascade configuration here
    category = db.relationship(
        'Category', 
        backref=db.backref('products', cascade='all, delete-orphan', passive_deletes=True)
    )

Skyler Chen · 0 rep · 16 hours ago

Your answer