0

I have the following table

  id | property_id | photo_url | is_best 
 ----|-------------|-----------|--------- 
   1 |           1 | test1     | true    
   2 |           1 | test2     | false   
   3 |           1 | test3     | false   
   4 |           2 | mest1     | true    
   5 |           2 | mest2     | false   
   6 |           3 | jest1     | false

where property_id is a foreign key. I need a constraint to restrict multiple true values for is_best column on a rows with same property_id

For instance restrict updating is_best = True for id = 2 or id = 3 or where id = 5

Ideally I need this feature in sqlalchemy. But postgres code also fine.

I am creating table in sqlalchemy like below:

class PropertyPhoto(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    property_id = db.Column(db.Integer,
                            db.ForeignKey('property.id'),
                            nullable=False,
                            onupdate='CASCADE',
                            ondelete='CASCADE')
    photo_url = db.Column(db.String, nullable=False, unique=True)
    is_best = db.Column(db.Boolean, default=False)
IamMashed
  • 1,811
  • 2
  • 21
  • 32

1 Answers1

2

In Postgres you can do this with a filtered unique index:

create unique index on the_table (property_id)
where is_best;