On Sun, Mar 11, 2018 at 6:42 PM, Ryan Holmes <ryan.xgame...@gmail.com> 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 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.<div 
> class="gmail_extra"><br><div class="gmail_quote">On Sun, Mar 11, 2018 at 6:42 
> PM, Ryan Holmes <span dir="ltr">&lt;<a href="mailto:ryan.xgame...@gmail.com"; 
> target="_blank">ryan.xgame...@gmail.com</a>&gt;</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&amp;q=https://stackoverflow.com/questions/49225846/&amp;source=gmail&amp;ust=1520901705467000&amp;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:&quot;Courier 
> New&quot;;font-size:9pt;color:rgb(0,0,128);font-weight:bold">from 
> </span><span style="color:rgb(0,0,0);font-family:&quot;Courier 
> New&quot;;font-size:9pt">sqlalchemy </span><span 
> style="font-family:&quot;Courier 
> New&quot;;font-size:9pt;color:rgb(0,0,128);font-weight:bold">import 
> </span><span style="color:rgb(0,0,0);font-family:&quot;Courier 
> New&quot;;font-size:9pt">create_engine</span></div><pre 
> style="color:rgb(0,0,0);font-family:&quot;Courier 
> New&quot;;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&amp;q=http://parent.id&amp;source=gmail&amp;ust=1520901705467000&amp;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&amp;q=http://child.id&amp;source=gmail&amp;ust=1520901705467000&amp;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&amp;q=http://parents.c.id&amp;source=gmail&amp;ust=1520901705467000&amp;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>
&nbsp;<br>
<a href="http://www.sqlalchemy.org/"; target="_blank"
data-saferedirecturl="https://www.google.com/url?hl=en&amp;q=http://www.sqlalchemy.org/&amp;source=gmail&amp;ust=1520901705467000&amp;usg=AFQjCNEf0hSS_sSiSAn1tysa5c_eZNpEAA";>http://www.sqlalchemy.org/</a><br>
&nbsp;<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&amp;q=http://stackoverflow.com/help/mcve&amp;source=gmail&amp;ust=1520901705467000&amp;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:sqlalchemy+unsubscr...@googlegroups.com";
target="_blank">sqlalchemy+unsubscribe@<wbr>googlegroups.com</a>.<br>
To post to this group, send email to <a
href="mailto:sqlalchemy@googlegroups.com";
target="_blank">sqlalchemy@googlegroups.com</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&amp;q=https://groups.google.com/group/sqlalchemy&amp;source=gmail&amp;ust=1520901705467000&amp;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&amp;q=https://groups.google.com/d/optout&amp;source=gmail&amp;ust=1520901705467000&amp;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 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.

Reply via email to