(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 [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.
