Re: [sqlalchemy] polymorphic inheritance and unique constraints

[Richard Gerd Kuesters | Pollux]

thanks again, Mike!

just a question: to make the constraint in the parent, shouldn't i move 
other columns that composes the constraint to the parent too?


cheers,
richard.

On 03/24/2015 10:33 AM, Michael Bayer wrote:

Richard Gerd Kuesters | Pollux <rich...@pollux.com.br> wrote:

hi all!

i'm dealing with a little problem here. i have a parent table and its two 
inheritances. there is a value that both children have and must be unique along 
either types. is there a way to move this column to the parent and use a 
constraint in the child? my implementation is postgres 9.4+ with psycopg2 only.
if this is single table inheritance then the constraint would most ideally
be placed on the parent class.

if you’re trying to make this “magic” such that you can semantically keep
the unique constraints on the child classes, you’d need to build out a
conditional approach within @declared_attr. IMO I think this is an idealized
edge case that in the real world doesn’t matter much - just do what works
(put the col / constraint on the base).

the approach is described at
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative/inheritance.html#resolving-column-conflicts.
You’d need to make this work for both the column and the constraint.


as a simple example (i'm just creating this example to simplify things), this 
works:
____________
class MyParent(Base):

     foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True)
     foo_name = Column(Unicode(64), nullable=False)
     foo_type = Column(Integer, nullable=False)

     __mapper_args__ = {
         "polymorphic_on": foo_type,
         "polymorphic_identity": 0
     }


class MyChild1(MyParent):

     foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
     bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False)
     child1_specific_name = Column(Unicode(5), nullable=False)
     child1_baz_stuff = Column(Boolean, default=False)

     __mapper_args__ = {
         "polymorphic_identity": 1
     }

     __table_args__ = (
         UniqueConstraint(bar_id, child1_specific_name,),  # works, bar_id is 
in MyChild1
     )


class MyChild2(MyParent):

     foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
     bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False)
     child2_specific_code = Column(UUID, nullable=False)
     child2_baz_stuff = Column(Float, nullable=False)
     
     __mapper_args__ = {
         "polymorphic_identity": 2
     }

     __table_args__ = (
         UniqueConstraint(bar_id, child2_specific_code,),  # works, bar_id is 
in MyChild2
     )
____________

but i would like to do this, if possible:
____________
class MyParent(Base):

     foo_id = Column(Integer, Sequence('foo_id_seq'), primary_key=True)
     foo_name = Column(Unicode(64), nullable=False)
     foo_type = Column(Integer, nullable=False)
     bar_id = Column(Integer, ForeignKey(AnotherEntity.bar_id), nullable=False) 
 # since both child uses bar_id, why not having it on the parent?

     __mapper_args__ = {
         "polymorphic_on": foo_type,
         "polymorphic_identity": 0
     }


class MyChild1(MyParent):

     foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
     child1_specific_name = Column(Unicode(5), nullable=False)
     child1_baz_stuff = Column(Boolean, default=False)

     __mapper_args__ = {
         "polymorphic_identity": 1
     }

     __table_args__ = (
         UniqueConstraint(MyParent.bar_id, child1_specific_name,),  # will it 
work?
     )


class MyChild2(MyParent):

     foo_id = Column(Integer, ForeignKey(MyParent.foo_id), primary_key=True)
     child2_specific_code = Column(UUID, nullable=False)
     child2_baz_stuff = Column(Float, nullable=False)
     
     __mapper_args__ = {
         "polymorphic_identity": 2
     }

     __table_args__ = (
         UniqueConstraint(MyParent.bar_id, child2_specific_code,),  # will it 
work?
     )
____________

well, will it work without being a concrete inheritance? :)


best regards,
richard.


--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.