CODEWITHSUNDEEP
flasksqlalchemyflask-sqlalchemy
Rukamakama
Rukamakama

Reputation: 1160

Flask SQLAlchemy unique constraint on multiple columns with a given value

Let say I have two tables

Order table

class Order(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  deliveries = db.relationship("Delivery", back_populates="order")

Delivery table

class Delivery(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  status= db.Column(db.String(20))
  order_id = db.Column(db.Integer, db.ForeignKey('order.id'), nullable=False)
  order = db.relationship("Order", back_populates="deliveries")

One order may have many deliveries. The status field of delivery table has following possible values ["Planned", "Failed", "Delivered"]

Question: How can make sure that one order does not have two planned deliveries ?

Tips: I know I can implemt unique constraint on multiple tables like adding below line to delivery schema

__table_args__ = (db.UniqueConstraint('order_id', 'status', name='_order_status_uc'))

But by doing so I will prevent one order from having two failed deliveries.

Any help will be greatly appreciate as I want to enforce this constraint directly in the db.

Upvotes: 1

Views: 1508

Answers (2)

van
van

Reputation: 76992

Update (given it is PostgreSQL)

Given you use PostgreSQL, the most straightforward solution is to use Partial Unique Index:

CREATE UNIQUE INDEX _order_status_uc ON delivery (order_id) WHERE status = 'Planned';

If you use SQLAlchemy to create the index, the below should be equivalent:

class Delivery(Base): # ...

__table_args__ = (
    Index(
        '_order_status_uc',
        'order_id',
        unique=True,
        postgresql_where=(status == 'Planned'),
    ),
)

Original answer

Assuming the RDBMS you are using supports this, one implementation idea would be to do as follows:

  • create a computed column on delivery table which would have value of order_id for "Planned" status and NULL otherwise
  • create a UNIQUE Constraint on this computed column.

This assumes the following regarding used RDBMS:

  1. Computed columns are not only supported but also can be part of the index/constraint.
  2. the implementation of UNIQUE index is such that multiple NULL values are allowed.

Please see PostgreSQL unique constraint null: Allowing only one Null article for more background.

Upvotes: 2

Andrew Clark
Andrew Clark

Reputation: 880

You could query your delivery model with the current order id and status planned, if there is a result, raise validation.

planned_delivery_exist_boolean = db.session.query(Delivery).filter(Delivery.order_id==order_id).filter(Delivery.status=="Planned").first()

if planned_delivery_exist_boolean:
    # trigger validation error

Upvotes: 0

Related Questions