SQLAlchemy unique constrain by field

Question

I have UniqueConstraint on field, but it wont allow me to add multiple entries (two is max!)

from sqlalchemy import Column, Integer, String, Boolean, UniqueConstraint

class Cart(SqlAlchemyBase):
    __tablename__ = 'cart'
    __table_args__ = (UniqueConstraint('is_latest'), {})
    sid = Column(Integer, primary_key=True)
    is_latest = Column(Boolean, index=True, nullable=False)
    name = Column(String)

I would like to support more entries, so that one name can have two variants:

name=foo, is_latest=True
name=foo, is_latest=False
name=bar, is_latest=True
name=bar, is_latest=False

but then reject any subsequent attempt to write name=foo (or bar) and is_latest=True

Thanks @Peter Works great, and maybe you know what if i wanted to have multiple False, but only one True? — mikazz, Jun 13 '22 at 13:57
I'd say it's probably possible, but I dunno how. Maybe instead of `is_latest` you have `version`, then it's easy to get the latest one and you can still add the constraint. — Peter, Jun 13 '22 at 14:25

score 0 · Answer 1 · answered Jun 13 '22 at 14:59

What you are trying to achieve here is a type 2 slowly changing dimension, this is a topic that has been discussed extensively and I encourage you to look it up.

When I look at your table you seem to use sid as a surrogate key, but I fail to see what is the natural key and what will be updated as time goes.

Anyway, there are several ways to achieve SCD type 2 result without the need to worry about your check, but the the simplest in my mind is to keep on adding records with your natural key and when querying, select only the one with highest surrogate key (autoincrementing integer), no need for current uniqueness here as only the latest value is fetched.

There are examples for versioning rows in SQLAlchemy docs, but since website come and go, I'll put a simplified draft of the above approach here.

class VersionedItem(Versioned, Base):
    id = Column(Integer, primary_key=True)  # surrogate key
    sku = Column(String, index=True)  # natural key
    price = Column(Integer)  # the value that changes with time

@event.listens_for(Session, "before_flush")
def before_flush(session, flush_context, instances):
    for instance in session.dirty:
        if not (
            isinstance(instance, VersionedItem)
            and session.is_modified(instance)
            and attributes.instance_state(instance).has_identity
        ):
            continue
        make_transient(instance)  # remove db identity from instance
        instance.id = None  # remove surrogate key
        session.add(instance)  # insert instance as new record

It looks complicated, thought that it could be resolved by simple CONSTRAINT CHECK — mikazz, Jun 13 '22 at 22:08
A check like “allow only one current for given natural key” would have to go over the table every time you insert data, which is not realistically feasible. Also what was your plan to maintain only one current record ? — ljmc, Jun 13 '22 at 22:10
Enforce in DB to only have one is_latest per some entity But how about partial unique index, that seems quite similar to what I need: https://stackoverflow.com/questions/27976683/creating-partial-unique-index-with-sqlalchemy-on-postgres — mikazz, Jun 14 '22 at 11:20
Enforcing on the DB side is one thing, but it leaves you to do all the manual work of marking the previous version's `is_latest` as false, which you either don't have to do if you get just latest one as in my answer, or can do with a `before_flush` event, as in the [`versioned_rows_w_versionid.html`](https://docs.sqlalchemy.org/en/14/_modules/examples/versioned_rows/versioned_rows_w_versionid.html) SQLAlchemy example. — ljmc, Jun 14 '22 at 13:04

score 0 · Answer 2 · answered Jun 14 '22 at 12:27

Looks like a Partial Unique Index can be used:

class Cart(SqlAlchemyBase):
    __tablename__ = 'cart'
    id = Column(Integer, primary_key=True)
    cart_id = Column(Integer)
    is_latest = Column(Boolean, default=False)
    name = Column(String)

    __table_args__ = (
        Index('only_one_latest_cart', name, is_latest,
              unique=True,
              postgresql_where=(is_latest)),
    )

name=foo, is_latest = True
name=foo, is_latest = False
name=bar, is_latest = False
name=bar, is_latest = False

And when adding another name=foo, is_latest = True

psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "only_one_latest_cart"
DETAIL:  Key (name, is_latest)=(foo, t) already exists.

SQLAlchemy unique constrain by field

2 Answers2