peewee manytomany - object a id and object b id on through table causes integrity error

Question

I am trying to save Tags within a Path to a through-table using a manytomany relationship, where Path is a list of Tags where tag might be a duplicate, like this:

path = ['div', 'div', 'div', 'div', 'div', 'ul', 'li', 'a']

If the Path above has an ID of 1, and I am storing Tags with a unique constraint, I expect the following in the through-table:

• path_id | tag_id
• 1______ 1
• 1______ 1
• 1______ 1
• 1______ 1
• 1______ 1
• 1______ 2
• 1______ 3
• 1______ 4

Where tag_id's 1,2,3 and 4 are div, ul, li and a respectively.

However, I get the following error:

peewee.IntegrityError: UNIQUE constraint failed: path_tag_through.path_id, path_tag_through.tag_id

What exactly am I doing wrong here? I can't set unique=False either.

Here is the code to replicate:

import peewee
from peewee import *
db = SqliteDatabase('bs.db')

class BaseModel(Model):
    class Meta:
        database = db

class Tag(BaseModel):
    name = CharField()

class Path(BaseModel):
    name = CharField()
    tags = ManyToManyField(Tag, backref='path')

PathTags = Path.tags.get_through_model()

db.create_tables([
    Tag,
    Path,
    PathTags])

my_path = ['div', 'div', 'div', 'div', 'div', 'ul', 'li', 'a']

path_id = Path.insert(name='my_path').execute()

path_obj = Path.get(path_id)

for i in my_path:
    path_obj.tags.add(i)

Answer 1

The many-to-many field assumes that the two foreign-keys comprising the relationship are unique. If you looked at the source code you'd see that there's a unique constraint on the two fks in the thru table. Your example is weird because you aren't talking about a set of relations but an ordering of things that you've normalized into a separate table for some insane reason (tag). In short, this is over-engineered all to hell.

If you insist on this schema, then create the through table explicitly without the unique constraint. You probably want to add a third column to specify the ordering, since it seems to be meaningful in your example.

Also your code is inefficient. Just do:

path_obj = Path.create(name='my_path')
path_obj.tags.add(my_path)