How to set the foreign key to a default value on delete?

Question

How to auto set the product category_id to a default value when category is deleted? For example 1 to point to the first category.

class Product(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80))
    content = db.Column(db.Text(), unique=True)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    atime = db.Column(db.DateTime())

    def __init__(self, name, content, category_id):
        self.name = name
        self.content = content
        self.category_id = category_id
        self.atime = datetime.now()

    def __repr__(self):
        return '<Product %r>' % self.id

class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80))
    products = db.relationship('Product', backref='category', cascade="all, delete, delete-orphan")

    def __init__(self, *args, **kwargs):
        if len(kwargs) > 0:
            self.name = kwargs['name']

    def __repr__(self):
        return '<Category %r>' % self.name

I don't want to use cascade to delete them!

Answer 1

There are two things that need to be performed in orchestration here:

1. Define the Foreign Key with proper referential action
2. Configure the cascade option of the SA relationship

I think the cleanest way would be to set the category_id to a NULL value when its Category is deleted: SET NULL is one of the possible Referential Actions of the ON DELETE clause, which you can add to your ForeignKey definition:

category_id = db.Column(db.Integer, db.ForeignKey('category.id', ondelete='SET NULL'))

In the same way you can use an option SET DEFAULT, but in this case you need to also configure the default value for the column category_id: category_id = Column(..., server_default=1). Note that implementations of these differ between different RDBMS.

As to the cascade option: You should basically remove the cascade="all, delete, delete-orphan" from your products relationship definition. In fact, you want to ensure that delete, delete-orphan are not there.

Having said that, you really need to test your code to cover different scenarios, as two different deletions of the Category object might produce different results depending on your RDBMS and SA configuration:

# scenario-1: delete in session: SA might set category_id of all chilren Products to None
c1 = session.query(Category).get(1)
session.delete(c1)
session.commit()

# scenario-2: delete without loading an object into the session: SA will perform no additional logic
session.query(Category).filter(Category.id == 2).delete()
session.commit()

Hope all this points you in the right direction. As usual, enabled SQL logging in your test code using echo=True or just by configuring logging module, and you will see what SA is doing to your database. Other changes which which you did not see in the SQL were done by RDBMS itself given your Referential Action.