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


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,
        "children": relation(
            primaryjoin=parent_children.c.parentID == parents.c.id,
            secondaryjoin=parent_children.c.childID == Child.id,

parent1 = Parent("Parent 1")

child1 = Child("Child1", "test1")
child2 = Child("Child2", "test2")
child3 = Child("Child3", "test3")

child4 = Child("Child4", "test1")


# up to here we should have 3 children

#adding this next one shoudl result in a constraint error, because it has the 
same col1 value as another in the collection (child1)

# removing child1 should allow child 4 to be added

SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


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