Stack Overflow Asked by 39fredy on December 18, 2021
I’m trying to have a table that has the following unique combination:
There could only be one entry with an account_id
that’s is set to active
, but multiple entries of that same account_id
in which active
is set to False
, meaning this is a possibility:
id | account_id | active | timestamp
0 | 12 | False | 2020-07-15 04:10:48.380781
1 | 12 | True | 2020-07-15 04:10:48.380781
2 | 12 | False |2020-07-15 04:10:48.380781
But this cannot:
id | account_id | active | timestamp
0 | 12 | False |2020-07-15 04:10:48.380781
1 | 12 | True |2020-07-15 04:10:48.380781
2 | 12 | True |2020-07-15 04:10:48.380781
I’m tying to use partial index to get this functionality but the caveat is that both account_id
and active
are inherited from a generic class. The classes look as follows:
class GenericClass:
id = Column(Integer, primary_key=True)
active = Column(Boolean, nullable=False,
doc='Whether this is active or not.'
@declared_attr # declared_attr decorator makes it like classmethod
def account_id(cls):
return Column(Integer, ForeignKey('account.account_id'))
Here is my actual table:
class AdvancedTable(Some.Model, GenericClass):
__versioned__ = {}
__table_args__ = (
Index('active_accid_index', 'account_id',
unique=True,
sqlite_where= GenericClass.active,
postgresql_where= GenericClass.active),
)
timestamp = Column(
DateTime, nullable=True, doc='DateTime this info was activated.')
But I am getting the following error:
sqlalchemy.exc.CompileError: Cannot compile Column object until its 'name' is assigned.
Can anyone help me achieve the functionality I would like to have without getting of the GenericClass
, (I’m using both postgresql and sqlite) ?
I believe I managed to overcome the issue by moving the index outside the table definition. See the code below. The relevant piece is highlighted.
Base = declarative_base()
class GenericClass(object):
@declared_attr
def account_id(cls):
return Column('account_id', ForeignKey('account.id'))
@declared_attr
def account(cls):
return relationship("Account")
active = Column(Boolean, nullable=False, doc='Whether this is active or not.')
class Account(Base):
__tablename__ = 'account'
id = Column(Integer, primary_key=True)
name = String(length=32)
class AdvancedTable(GenericClass, Base):
__versioned__ = {}
__tablename__ = "advanced"
id = Column(Integer, primary_key=True)
timestamp = Column(
DateTime, nullable=True, doc='DateTime this info was activated.')
my_index = Index('active_accid_index', AdvancedTable.account_id, unique = True,
sqlite_where = AdvancedTable.active )
# ===== This is the key part =====
engine = create_engine('sqlite:///advanced.db')
Base.metadata.create_all(engine)
The resulting schema, for sqlite, is:
CREATE TABLE account (
id INTEGER NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE advanced (
active BOOLEAN NOT NULL,
id INTEGER NOT NULL,
timestamp DATETIME,
account_id INTEGER,
PRIMARY KEY (id),
CHECK (active IN (0, 1)),
FOREIGN KEY(account_id) REFERENCES account (id)
);
CREATE UNIQUE INDEX active_accid_index ON advanced (account_id) WHERE active;
Answered by Roy2012 on December 18, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP