0

I have the following model

class Card(Base):
    __tablename__ = 'Cards'
    __table_args__ = (
        db.Index(
            'one_active_code_only',
            'code', 'company',
            unique=True,
            postgresql_where='used_by is NULL'
        ),
        db.CheckConstraint(
            '(used_by IS NULL AND used_at IS NULL) OR (used_by IS NOT NULL AND used_at IS NOT NULL)',
            name='used_by and used_at must be set together'
        ),
        db.CheckConstraint(
            '(booked_by IS NULL AND booked_at IS NULL) OR (booked_by IS NOT NULL AND booked_at IS NOT NULL)',
            name='booked_by and booked_by must be set together'
        ),
    )

    name = db.Column(db.String(100), nullable=False)
    code = db.Column(db.String(100), nullable=False)
    value = db.Column(db.Numeric, nullable=False)
    company = db.Column(db.ForeignKey('Companies.id'), nullable=False)
    img = db.Column(db.String, nullable=True)

    booked_at = db.Column(db.DateTime, nullable=True)
    booked_by = db.Column(db.ForeignKey('Users.id'), nullable=True)

    used_at = db.Column(db.DateTime, nullable=True)
    used_by = db.Column(db.ForeignKey('Users.id'), nullable=True)

Now I want constraints used_by and booked_by to be unchangeable after the first value set.

How can I achieve that?

I don't want to implement the before-query mechanism in the server, I want to implement it within the database directly, to prevent changing data from another database client.

requirements.txt

Flask==2.1.1
Flask-SQLAlchemy==2.5.1
SQLAlchemy==1.4.35
...
...
...
Ayad Salim
  • 88
  • 7
  • 1
    Does this answer your question? [sqlalchemy: how to block updates on a specific column](https://stackoverflow.com/questions/23455506/sqlalchemy-how-to-block-updates-on-a-specific-column) – Jeremy Apr 23 '22 at 19:53
  • If you want to do it at the database level then you'll have to use a trigger - for example see [this Q&A](https://stackoverflow.com/questions/56082230/postgres-trigger-to-avoid-update-in-a-column). Assuming the column starts as `NULL` you would allow update if the old value is `NULL`, otherwise reject. – snakecharmerb Apr 24 '22 at 08:11

0 Answers0