SQLAlchemy: Any constraint to check one of the two columns is not null?

Question

This may be totally stupid thing to ask but I have such a requirement in my model where atleast either category or parent_category is not null

My model looks like

class BudgetCategories(db.Model):
    __tablename__ = 'budget_categories'
    uuid = Column('uuid', GUID(), default=uuid.uuid4, primary_key=True,
                  unique=True)
    budget_id = Column(GUID(), ForeignKey('budgets.uuid'), nullable=False)
    budget = relationship('Budget', backref='budgetCategories')
    category = Column('category', sa.types.String, nullable=True)
    parent_category = Column('parent_category', sa.types.String, nullable=True)
    amount = Column('amount', Numeric(10, 2), nullable=False)
    recurring = Column('recurring', sa.types.Boolean,
                       nullable=False)
    created_on = Column('created_on', sa.types.DateTime(timezone=True),
                        nullable=False)

How can I specify that. I don't even know what to try

Any pointers appreciated

I am using PostgreSQL as the backend database

At least one of `category` and `parent_category` is `not null` or exactly one of them is `not null`? — mu is too short, Jan 06 '14 at 06:02
Atleast. if both are populated -> good, but atleast either category or parent_category is not null. I hope that clarifies — daydreamer, Jan 06 '14 at 06:08
a `category is not null or parent_category is not null` CHECK constraint would do it at the database level, not sure about the Python level though. — mu is too short, Jan 06 '14 at 07:26

score 20 · Answer 1 · answered Jan 06 '14 at 10:00

20

I am not 100% sure about the PostgreSQL syntax, but following addition to your BudgetCategories model should do the trick using CheckConstraint:

class BudgetCategories(Base):
    __tablename__ = 'budget_categories'
    # ...

    # @note: new
    __table_args__ = (
            CheckConstraint('NOT(category IS NULL AND parent_category IS NULL)'),
            )

answered Jan 06 '14 at 10:00

van

74,297
13
168
171

Maybe you can help me. What about in cases where you have more than 2 fields and only one of them should have a value, while the rest should be null? Thanks – ChickenFeet Apr 12 '17 at 02:21
1

@ChickenFeet: consider [this](http://stackoverflow.com/a/15180124/99594) or [this](https://dba.stackexchange.com/a/5279/25414) answers – van Apr 14 '17 at 19:07

score 7 · Answer 2 · answered Sep 09 '20 at 08:25

I needed XOR behavior in my SQLalchemy models. I come up with the following definition (backend used: PostgreSQL):

from sqlalchemy.schema import (
    CheckConstraint
)

class ScheduledNotebook(Base):
    __table_args__ = (
        (CheckConstraint('(uuid::text IS NULL) <> (notebook_path IS NULL)', name='uuid_xor_notebook_path')),
    )

    id = Column(Integer, primary_key=True)
    notebook_path = Column(String, nullable=True, unique=True)
    uuid = Column(UUID(as_uuid=True), primary_key=True, unique=True, nullable=True)

and following alembic migration (note: autogenerate won't detect it - you have to add it manually):

def upgrade():
    op.create_check_constraint(
        'uuid_xor_notebook_path',
        table_name='scheduled_notebooks',
        schema='metadata',
        condition='(uuid::text IS NULL) <> (notebook_path IS NULL)'
    )


def downgrade():
    op.drop_constraint('uuid_xor_notebook_path')

and it works like a charm:

- only notebook_path - OK

datalake=#  INSERT INTO scheduled_notebooks (schedule,enabled,owner, notebook_path) VALUES ('{"kind":"hourly"}',true,'akos', '/a/b/c/d/e.ipynb');
INSERT 0 1

- only uuid - OK

datalake=#  INSERT INTO scheduled_notebooks (schedule,enabled,owner, uuid) VALUES ('{"kind":"hourly"}',true,'akos', '7792bd5f-5819-45bf-8902-8cf43102434d');
INSERT 0 1

- both uuid and notebook_path - FAILS as desired

datalake=#  INSERT INTO scheduled_notebooks (schedule,enabled,owner, uuid, notebook_path) VALUES ('{"kind":"hourly"}',true,'akos', '7792bd5f-5819-45bf-8902-8cf43102434f', '/a/b/c/d');
ERROR:  new row for relation "scheduled_notebooks" violates check constraint "uuid_xor_notebook_path"
DETAIL:  Failing row contains (567, /a/b/c/d, {"kind": "hourly"}, t, akos, null, null, null, 7792bd5f-5819-45bf-8902-8cf43102434f).

- neither uuid nor notebook_path - FAILS as desired

datalake=#  INSERT INTO scheduled_notebooks (schedule,enabled,owner) VALUES ('{"kind":"hourly"}',true,'akos');
ERROR:  new row for relation "scheduled_notebooks" violates check constraint "uuid_xor_notebook_path"
DETAIL:  Failing row contains (568, null, {"kind": "hourly"}, t, akos, null, null, null, null).

Thanks, works for flask-sqlalchemy as well. – flashton Dec 29 '22 at 06:25 — flashton, Dec 29 '22 at 06:25

score 3 · Answer 3 · answered Jun 02 '17 at 11:41

3

I hope is not too late but this should do the trick and it is checked to work with a PostGreSQL DB:

class BudgetCategories(Base):
    __tablename__ = 'budget_categories'
    __table_args__ = (
        CheckConstraint('coalesce(category , parent_category ) is not null'),
    )
    # ...

answered Jun 02 '17 at 11:41

DaniGate

166
1
5

2

Can anyone please explain why it's down voted? Is it because of some performance implications I am not aware of? Maybe DB back-end support? I am really curious, because I personally find this solution nifty. – radzak Aug 02 '19 at 10:58
I'd be interested to know @radzak. Maybe because it's less obvious to read? Very debatable. I can't say it's faulty. – youri Mar 18 '20 at 10:05
1

A bit more of explanation would be nice. For those wondering, `COALESCE` is a Postgres function that will return the first `NOT NULL` element of the arguments or `NULL` if all arguments are `NULL`. https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-coalesce/ – Ramon Dias Jul 31 '22 at 13:58

score 2 · Answer 4 · answered Nov 19 '21 at 11:07

2

When using the PostgreSQL backend, you can also use the num_nonnulls function for this purpose:

class BudgetCategories(Base):
    __tablename__ = 'budget_categories'
    __table_args__ = (
        CheckConstraint('num_nonnulls(category, parent_category) = 1'),
    )

answered Nov 19 '21 at 11:07

msh

23
4

I like it, but it would fail if both of the columns are `NOT NULL`. You could change it to `num_nonnulls() > 0` or `num_nulls() = 0` – Ramon Dias Jul 31 '22 at 14:05

score 0 · Answer 5 · answered Oct 27 '22 at 13:26

I came across this problem some weeks ago, too and was provided with the solution here. So translated to sqlalchemy I'd guess (not yet tested!!):

from sqlalchemy.schema import CheckConstraint

class MyClass(Base):
    __table_args__ = (
        (CheckConstraint(
            'num_nulls(nullif(trim(col_a::text), ''), nullif(trim(col_b::text), '')) = 1', 
            name='uuid_xor_notebook_path')),
    )
    id = Column(Integer, primary_key=True)
    col_a = Column(String, nullable=True, unique=True)
    col_b = Column(String, nullable=True, unique=True)

However for aesthetic reasons I prefer @andilabs solution. To still take into account empty fields for this reason, one could leverage another solution from here and write:

from sqlalchemy.schema import CheckConstraint

class MyClass(Base):
    __table_args__ = (
        (CheckConstraint(
            '(col_a::text IS NULL) <> (col_b IS NULL)', 
            name='uuid_xor_notebook_path')),
    )
    id = Column(Integer, primary_key=True)
    col_a = Column(String, nullable=True, unique=True)
    col_b = Column(String, nullable=True, unique=True)

    @validates('col_a', 'col_b')
    def empty_string_to_null(self, key, value):
        if isinstance(value, str) and value == '':
            return None
        else:
            return value

Not really the shortest of all snippets, I admit...

whoops, I copy-pasted code snippets from above to save me some typing. That's why the constraint-name is somewhat stupid in my example. but you get the idea... — Robert, Oct 27 '22 at 13:28

SQLAlchemy: Any constraint to check one of the two columns is not null?

5 Answers5

Linked

Related