Re: [sqlalchemy] SQLlite: enforce a unique constraint on a relation?

[Ryan Holmes]

Thanks Mike,

While I would rather this be a hard constraint on the database, not 
enforced in python, I understand that SQLite is pretty limited in this 
regard, so I'm down to try anything really. I also had the idea of using a 
custom collection simply because the project already uses them heavily. I 
will look into possibly creating triggers for the database to check before 
insert (if that's a thing in SQLite), and also SQLAlchemy validators.

Thanks for the resources! Would be interested in hearing other solutions 
that people might come up with :)

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

Re: [sqlalchemy] SQLlite: enforce a unique constraint on a relation?

[Mike Bayer]

On Sun, Mar 11, 2018 at 6:42 PM, Ryan Holmes  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. class="gmail_extra">On Sun, Mar 11, 2018 at 6:42 
> PM, Ryan Holmes mailto:ryan.xgame...@gmail.com; 
> target="_blank">ryan.xgame...@gmail.com wrote: class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc 
> solid;padding-left:1ex">(cross posted from  href="https://stackoverflow.com/questions/49225846/;