title: Flask-SQLAlchemy validation: prevent adding to relationship based on other field
body: <p>I'm using flask-sqlalchemy and trying to validate input to my database. For single fields, the validate decorator works well. However, I'm having issues with preventing values from being added to a collection/relationship based the value of on another field. For example, consider the following data model:</p>
  <pre class="lang-py prettyprint-override"><code>class MyRelation(db.Model):
      __tablename__ = &quot;my_relation&quot;
      id = db.Column(db.Integer, primary_key=True)
      my_data_id = db.Column(db.Integer, db.ForeignKey(&quot;my_data.id&quot;))
      some_value = db.Column(db.String)
  
  class MyData(db.Model):
      __tablename__ = &quot;my_data&quot;
      id = db.Column(db.Integer, primary_key=True)
      some_relation = db.relationship(&quot;MyRelation&quot;, backref=&quot;my_data&quot;)
      some_other_value = db.Column(db.Boolean)
  </code></pre>
  <p>The problem I'm running into is I want to ensure <code>some_relation</code> is empty if <code>some_other_value == True</code>. I have looked into the following options. I'm not sure if any of these could be tuned to provide the intended functionality:</p>
  <ol>
  <li>Use <code>@validates</code> with multiple fields as <a href="https://stackoverflow.com/a/33025472/6814713">specified here</a>. The problem with this approach is it seems to validate the collection one by one, and returning None in validation for poorly formed input yields <code>sqlalchemy.orm.exc.FlushError: Can't flush None value found in collection Job.alternate_attributes</code>. Returning an empty list also doesn't work.</li>
  <li>Use a <code>before_insert</code> event listener, as <a href="https://stackoverflow.com/a/45147957/6814713">outlined here</a>. The challenge I see with this is this seems like this would not be compatible with relationship events per the <a href="https://docs.sqlalchemy.org/en/14/orm/session_events.html#mapper-level-events" rel="nofollow noreferrer">official docs</a>.</li>
  <li>Use a <code>before_flush</code> event listener, which sounds like it might be compatible with this use case, but I'll be honest I'm having a hard time finding a good example of exactly how to use this given my goal. It's pretty abstract, and I don't see any examples in the docs (<a href="https://docs.sqlalchemy.org/en/14/orm/examples.html#examples-versioned-history" rel="nofollow noreferrer">both</a> of the <a href="https://docs.sqlalchemy.org/en/14/orm/examples.html#module-examples.versioned_rows" rel="nofollow noreferrer">examples</a> mentioned at the end of the <a href="https://docs.sqlalchemy.org/en/14/orm/session_events.html#before-flush" rel="nofollow noreferrer">before_flush section</a> don't actually contain any reference to the <code>before_flush</code> method they're supposedly demonstrating).</li>
  </ol>
  <p>Any guidance would be much appreciated - thanks!</p>