postgresql sqlalchemy core define constraint to a combination of the columns at a time
<p>I have a model defined as:</p>

<pre><code>groups_categories = Table("groups_categories", metadata,
                        Column("id", Integer, primary_key=True),
                        Column("group", Integer, ForeignKey("groups.id")),
                        Column("dept", Integer, ForeignKey("departments.id")),
                        Column("category", Integer,ForeignKey("categories.id")),
                        Column("allow_view", Boolean, default=True),
                        Column("allow_edit", Boolean, default=True),
                        Column("allow_download", Boolean, default=True),
                        UniqueConstraint('dept','category',
                                         name='dept_category'),
                        UniqueConstraint('group','category',
                                         name='group_category'))
</code></pre>

<p>I would like to define a constraint such that for a given category, I can have either dept or group value but not both. 
How can this check be implemented at this model level definition itself? I am using <strong>sqlalchemy core</strong> only</p>

<p>If I understood you correctly, you're looking for a <a href="http://docs.sqlalchemy.org/en/latest/core/constraints.html#check-constraint" rel="nofollow"><code>CheckConstraint</code></a> such that both <em>group</em> and <em>dept</em> cannot be non-null values at the same time:</p>

<pre><code>CHECK (NOT ("group" IS NOT NULL AND dept IS NOT NULL))
</code></pre>

<p>By applying <a href="https://en.wikipedia.org/wiki/De_Morgan%27s_laws" rel="nofollow">De Morgan's law</a> this can be simplified a bit:</p>

<pre><code>-- either group or dept must be null
CHECK ("group" IS NULL OR dept IS NULL)
</code></pre>

<p>And your <code>Table</code> definition becomes:</p>

<pre><code>from sqlalchemy import CheckConstraint

groups_categories = Table(
    "groups_categories", metadata,
    Column("id", Integer, primary_key=True),
    Column("group", Integer, ForeignKey("groups.id")),
    Column("dept", Integer, ForeignKey("departments.id")),
    Column("category", Integer, ForeignKey("categories.id")),
    Column("allow_view", Boolean, default=True),
    Column("allow_edit", Boolean, default=True),
    Column("allow_download", Boolean, default=True),
    UniqueConstraint('dept', 'category',
                     name='dept_category'),
    UniqueConstraint('group', 'category',
                     name='group_category'),
    # The CHECK constraint
    CheckConstraint('"group" IS NULL OR dept IS NULL',
                    name='check_group_dept')
)
</code></pre>

python postgresql sqlalchemy