On Sun, Mar 11, 2018 at 6:42 PM, Ryan Holmes <[email protected]> wrote: > (cross posted from https://stackoverflow.com/questions/49225846/) > > Lets say I have 3 tables: parent, child, and a linker table for the two for > a many-to-many relationship, `parentChildren`. Each parent can have multiple > children, and each child can have multiple parents. If parent 1 already has > child 1, another link between these two cannot be inserted (this is easily > done by making both parentID and childID as part of the primary key) > > However, I would like to enforce another constraint: each parent can only > have children with a unique `col1`. so, lets say that 4 children exists, > each with a `col1` different than the other, except the last one, which has > the same `col1` as the first child. > > If I add child 1 then try to add child 2, that should be fine since they do > not share the same `col1`. However, if I add child 1 and child 4, I want to > get a constraint error. > > Here's the code: (using Classic mappings, not Declarative. This question > relates to an older project that hasn't been updated to use declarative > syntax yet) > > from sqlalchemy import create_engine > > from sqlalchemy.orm import relation, mapper > from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey > > > engine = create_engine('sqlite:///:memory:', echo=True) > > metadata = MetaData() > parents = Table('parent', metadata, > Column('id', Integer, primary_key=True), > Column('name', String), > ) > > children = Table('child', metadata, > Column('id', Integer, primary_key=True), > Column('name', String), > Column('col1', String), > ) > > parent_children = Table('parentChildren', metadata, > Column('parentID', ForeignKey("parent.id"), primary_key=True), > Column('childID', ForeignKey("child.id"), primary_key=True), > ) > > metadata.create_all(engine) > > class Parent(object): > def __init__(self, name): > self.name = name > self.children = [] > > class Child(object): > def __init__(self, name, col1): > self.name = name > self.col1 = col1 > > mapper(Child, children) > mapper(Parent, parents, > properties={ > "children": relation( > Child, > cascade='all,delete-orphan', > backref='parent', > single_parent=True, > primaryjoin=parent_children.c.parentID == parents.c.id, > secondaryjoin=parent_children.c.childID == Child.id, > secondary=parent_children > ), > } > ) > > parent1 = Parent("Parent 1") > > child1 = Child("Child1", "test1") > child2 = Child("Child2", "test2") > child3 = Child("Child3", "test3") > > child4 = Child("Child4", "test1") > > parent1.children.append(child1) > parent1.children.append(child2) > parent1.children.append(child3) > > # up to here we should have 3 children > print(parent1.children) > > #adding this next one shoudl result in a constraint error, because it has > the same col1 value as another in the collection (child1) > parent1.children.append(child4) > > # removing child1 should allow child 4 to be added > print(parent1.children)
quickest way is to denormalize and copy child.col1 into parentChildren and make that part of the constraint. other than that you'd need to use a trigger, since you're looking for "constraint violation" which implies server-side. Since you have this as a SQLAlchemy question, if you're looking for a Python side exception, probably validating the collection using @validates: http://docs.sqlalchemy.org/en/latest/orm/mapped_attributes.html?highlight=validates#simple-validators, or looking and checking these collections during the before_flush event: http://docs.sqlalchemy.org/en/latest/orm/session_events.html#before-flush or you can go all the way and build a custom collection: http://docs.sqlalchemy.org/en/latest/orm/collections.html?highlight=collections#custom-collection-implementations > > -- > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout.<div > class="gmail_extra"><br><div class="gmail_quote">On Sun, Mar 11, 2018 at 6:42 > PM, Ryan Holmes <span dir="ltr"><<a href="mailto:[email protected]"; > target="_blank">[email protected]</a>></span> wrote:<br><blockquote > class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc > solid;padding-left:1ex"><div dir="ltr"><div>(cross posted from <a > href="https://stackoverflow.com/questions/49225846/"; target="_blank" > data-saferedirecturl="https://www.google.com/url?hl=en&q=https://stackoverflow.com/questions/49225846/&source=gmail&ust=1520901705467000&usg=AFQjCNGGRM9B6vIu7L8Ey7Z79q69nx2FCQ";>https://stackoverflow.com/<wbr>questions/49225846/</a>)</div><div><br></div><div>Lets > say I have 3 tables: parent, child, and a linker table for the two for a > many-to-many relationship, `parentChildren`. Each parent can have multiple > children, and each child can have multiple parents. If parent 1 already has > child 1, another link between these two cannot be inserted (this is easily > done by making both parentID and childID as part of the primary > key)</div><div><br></div><div>However, I would like to enforce another > constraint: each parent can only have children with a unique `col1`. so, lets > say that 4 children exists, each with a `col1` different than the other, > except the last one, which has the same `col1` as the first > child.</div><div><br></div><div>If I add child 1 then try to add child 2, > that should be fine since they do not share the same `col1`. However, if I > add child 1 and child 4, I want to get a constraint > error.</div><div><br></div><div>Here's the code: (using Classic mappings, not > Declarative. This question relates to an older project that hasn't been > updated to use declarative syntax yet)</div><div><br></div><div><span > style="font-family:"Courier > New";font-size:9pt;color:rgb(0,0,128);font-weight:bold">from > </span><span style="color:rgb(0,0,0);font-family:"Courier > New";font-size:9pt">sqlalchemy </span><span > style="font-family:"Courier > New";font-size:9pt;color:rgb(0,0,128);font-weight:bold">import > </span><span style="color:rgb(0,0,0);font-family:"Courier > New";font-size:9pt">create_engine</span></div><pre > style="color:rgb(0,0,0);font-family:"Courier > New";font-size:9pt"><span style="color:#000080;font-weight:bold">from > </span>sqlalchemy.orm <span style="color:#000080;font-weight:bold">import > </span>relation, mapper<br><span style="color:#000080;font-weight:bold">from > </span>sqlalchemy <span style="color:#000080;font-weight:bold">import > </span>Table, Column, Integer, String, MetaData, ForeignKey<br><br><br>engine > = create_engine(<span > style="color:#008080;font-weight:bold">'sqlite:///:<wbr>memory:'</span>, > <span style="color:#660099">echo</span>=<span > style="color:#000080;font-weight:bold">True</span>)<br><br>metadata = > MetaData()<br>parents = Table(<span > style="color:#008080;font-weight:bold">'parent'</span>, metadata,<br> > Column(<span style="color:#008080;font-weight:bold">'id'</span>, Integer, > <span style="color:#660099">primary_key</span>=<span > style="color:#000080;font-weight:bold">True</span>),<br> Column(<span > style="color:#008080;font-weight:bold">'name'</span>, > String),<br>)<br><br>children = Table(<span > style="color:#008080;font-weight:bold">'child'</span>, metadata,<br> > Column(<span style="color:#008080;font-weight:bold">'id'</span>, Integer, > <span style="color:#660099">primary_key</span>=<span > style="color:#000080;font-weight:bold">True</span>),<br> Column(<span > style="color:#008080;font-weight:bold">'name'</span>, String),<br> > Column(<span style="color:#008080;font-weight:bold">'col1'</span>, > String),<br>)<br><br>parent_children = Table(<span > style="color:#008080;font-weight:bold">'parentChildren'</span>, metadata,<br> > Column(<span style="color:#008080;font-weight:bold">'parentID'</span>, > ForeignKey(<span style="color:#008080;font-weight:bold">"<a > href="http://parent.id"; target="_blank" > data-saferedirecturl="https://www.google.com/url?hl=en&q=http://parent.id&source=gmail&ust=1520901705467000&usg=AFQjCNHO1ZEi8Y_B0kitRyiJBsFJT_MXHQ";>parent.id</a>"</span>), > <span style="color:#660099">primary_key</span>=<span > style="color:#000080;font-weight:bold">True</span>),<br> Column(<span > style="color:#008080;font-weight:bold">'childID'</span>, ForeignKey(<span > style="color:#008080;font-weight:bold">"<a href="http://child.id"; > target="_blank" > data-saferedirecturl="https://www.google.com/url?hl=en&q=http://child.id&source=gmail&ust=1520901705467000&usg=AFQjCNFE3GBpZ6hSq4RQWpjeKe1RSRy-Bg";>child.id</a>"</span>), > <span style="color:#660099">primary_key</span>=<span > style="color:#000080;font-weight:bold">True</span>),<br>)<br><br>metadata.create_all(engine)<br><br><span > style="color:#000080;font-weight:bold">class </span>Parent(<span > style="color:#000080">object</span>):<br> <span > style="color:#000080;font-weight:bold">def </span><span > style="color:#b200b2">__init__</span>(<span > style="color:#94558d">self</span>, name):<br> <span > style="color:#94558d">self</span>.name = name<br> <span > style="color:#94558d">self</span>.children = []<br><br><span > style="color:#000080;font-weight:bold">class </span>Child(<span > style="color:#000080">object</span>):<br> <span > style="color:#000080;font-weight:bold">def </span><span > style="color:#b200b2">__init__</span>(<span > style="color:#94558d">self</span>, name, col1):<br> <span > style="color:#94558d">self</span>.name = name<br> <span > style="color:#94558d">self</span>.col1 = col1<br><br>mapper(Child, > children)<br>mapper(Parent, parents,<br> <span > style="color:#660099">properties</span>={<br> <span > style="color:#008080;font-weight:bold">"children"</span>: relation(<br> > Child,<br> <span style="color:#660099">cascade</span>=<span > style="color:#008080;font-weight:bold">'all,delete-orphan'</span>,<br> > <span style="color:#660099">backref</span>=<span > style="color:#008080;font-weight:bold">'parent'</span>,<br> <span > style="color:#660099">single_parent</span>=<span > style="color:#000080;font-weight:bold">True</span>,<br> <span > style="color:#660099">primaryjoin</span>=parent_children.c.<wbr>parentID == > <a href="http://parents.c.id"; target="_blank" > data-saferedirecturl="https://www.google.com/url?hl=en&q=http://parents.c.id&source=gmail&ust=1520901705467000&usg=AFQjCNHIRnbZozkRXyhCEJEOg5FJRzQsFw";>parents.c.id</a>,<br> > <span > style="color:#660099">secondaryjoin</span>=parent_children.<wbr>c.childID == > Child.id,<br> <span > style="color:#660099">secondary</span>=parent_children<br> ),<br> > }<br>)<br><br>parent1 = Parent(<span > style="color:#008080;font-weight:bold">"Parent 1"</span>)<br><br>child1 = > Child(<span style="color:#008080;font-weight:bold">"Child1"</span>, <span > style="color:#008080;font-weight:bold">"test1"</span>)<br>child2 = > Child(<span style="color:#008080;font-weight:bold">"Child2"</span>, <span > style="color:#008080;font-weight:bold">"test2"</span>)<br>child3 = > Child(<span style="color:#008080;font-weight:bold">"Child3"</span>, <span > style="color:#008080;font-weight:bold">"test3"</span>)<br><br>child4 = > Child(<span style="color:#008080;font-weight:bold">"Child4"</span>, <span > style="color:#008080;font-weight:bold">"test1"</span>)<br><br>parent1.children.append(<wbr>child1)<br>parent1.children.append(<wbr>child2)<br>parent1.children.append(<wbr>child3)<br><br><span > style="color:#808080;font-style:italic"># up to here we should have 3 > children<br></span><span > style="color:#000080">print</span>(parent1.children)<br><br><span > style="color:#808080;font-style:italic">#adding this next one shoudl result > in a constraint error, because it has the same col1 value as another in the > collection > (child1)<br></span>parent1.children.append(<wbr>child4)<br><br><span > style="color:#808080;font-style:italic"># removing child1 should allow child > 4 to be added<br></span><span > style="color:#000080">print</span>(parent1.children)</pre></div> <p></p> -- <br> SQLAlchemy - <br> The Python SQL Toolkit and Object Relational Mapper<br> <br> <a href="http://www.sqlalchemy.org/"; target="_blank" data-saferedirecturl="https://www.google.com/url?hl=en&q=http://www.sqlalchemy.org/&source=gmail&ust=1520901705467000&usg=AFQjCNEf0hSS_sSiSAn1tysa5c_eZNpEAA";>http://www.sqlalchemy.org/</a><br> <br> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See <a href="http://stackoverflow.com/help/mcve"; target="_blank" data-saferedirecturl="https://www.google.com/url?hl=en&q=http://stackoverflow.com/help/mcve&source=gmail&ust=1520901705467000&usg=AFQjCNHv28u6GMRfeXUSM7psnsjAIcYUyw";>http://stackoverflow.com/help/<wbr>mcve</a> for a full description.<br> --- <br> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.<br> To unsubscribe from this group and stop receiving emails from it, send an email to <a href="mailto:[email protected]"; target="_blank">sqlalchemy+unsubscribe@<wbr>googlegroups.com</a>.<br> To post to this group, send email to <a href="mailto:[email protected]"; target="_blank">[email protected]</a>.<br> Visit this group at <a href="https://groups.google.com/group/sqlalchemy"; target="_blank" data-saferedirecturl="https://www.google.com/url?hl=en&q=https://groups.google.com/group/sqlalchemy&source=gmail&ust=1520901705467000&usg=AFQjCNENgK6AYj8EMS5JZTuTNfj08Ln4rA";>https://groups.google.com/<wbr>group/sqlalchemy</a>.<br> For more options, visit <a href="https://groups.google.com/d/optout"; target="_blank" data-saferedirecturl="https://www.google.com/url?hl=en&q=https://groups.google.com/d/optout&source=gmail&ust=1520901705467000&usg=AFQjCNFh3qh72lEb6Xgs_Zd2JUWlmdfABw";>https://groups.google.com/d/<wbr>optout</a>.<br> </blockquote></div><br></div> -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
