CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
user956424
user956424

Reputation: 1609

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

Upvotes: 0

Views: 1191

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52997

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

Upvotes: 1

Related Questions