Hello Mike,
Il 08/01/19 19:00, Mike Bayer ha scritto:
On Tue, Jan 8, 2019 at 12:40 PM Riccardo Magliocchetti
<riccardo.magliocche...@gmail.com> wrote:
Hello,
I'd like to implement soft delete so I have to take into account when creating
constraints
the softdelete boolean column. The trick i want to implement is to get null for
the column
when the row is supposed to be deleted. When creating the index by hand in SQL
it works
just nice but in sqlalchemy i get an exception. SQLAlchemy is at 1.2.15.
This is how i define the contstraint:
class Foo(sa.Model):
__table_args__ = (
sa.UniqueConstraint('foobar_id', sa.func.nullif('deleted', True)),
)
So you definitely can't put a string column name inside of
func.nullif, that needs to be a Column object, overall Column is
better to use for this rather than strings, so in *theory*, this would
look like:
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
foobar_id = Column(Integer)
deleted = Column(Boolean)
__table_args__ = (
UniqueConstraint(foobar_id, func.nullif(deleted, True)),
)
however UniqueContraint doesn't support functions right now. Index
does, so easiest solution is to use that instead with unique=True:
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
foobar_id = Column(Integer)
deleted = Column(Boolean)
__table_args__ = (
Index("my_idx", foobar_id, func.nullif(deleted, True), unique=True),
)
thanks, I will give it a try tomorrow!
I'm not sure what database you are on (please specify). I can't get a
I'm on PostgreSQL, thought i had written it sorry.
functional UNIQUE constraint to work on Postgresql, but for the index
it looks like:
CREATE UNIQUE INDEX my_idx ON a (foobar_id, nullif(deleted, true))
That's exactly what i've tried by hand
Per
https://stackoverflow.com/questions/23542794/postgres-unique-constraint-vs-index,
there is virtually no difference between these (or at least it is very
hard to tell them apart) if this is in fact PG, but perhaps you're on
SQL Server, not sure.
That's my understanding too
We can add support for functions in UniqueConstraint as well if you
want to file an enhancement ticket at
https://github.com/sqlalchemy/sqlalchemy.
Will do
If you must use UNIQUE constraint and not UNIQUE INDEX, for now you'd
have to use DDL():
event.listen(
A.__table__, "after_create",
DDL("ALTER TABLE a ADD CONSTRAINT my_uq UNIQUE (foobar_id,
nullif(deleted, true))")
)
Thanks
--
Riccardo Magliocchetti
@rmistaken
http://menodizero.it
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
