(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)

-- 
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