user956424 user956424 - 2 months ago 15
Python Question

postgresql sqlalchemy core define constraint to a combination of the columns at a time

I have a model defined as:

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'))


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 sqlalchemy core only

Answer

If I understood you correctly, you're looking for a CheckConstraint such that both group and dept cannot be non-null values at the same time:

CHECK (NOT ("group" IS NOT NULL AND dept IS NOT NULL))

By applying De Morgan's law this can be simplified a bit:

-- either group or dept must be null
CHECK ("group" IS NULL OR dept IS NULL)

And your Table definition becomes:

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')
)