How can I insert multiple time a many-to-many relation with flask-sqlalchemy?

Question

I am using flask-sqlalchemy in order to create a RestAPI. The idea is to store playlists with one or several medias inside it. To link these two, I am using a many-to-many relationship.

Sometimes, a same media will be inserted twice inside a playlist but it fails because the relationship table already has the entry.

class Media(db.Model):
    __tablename__ = 'media'
    id = db.Column(db.Integer, primary_key=True)

medias = db.Table('media_playlist',
                  db.Column('media_id', db.Integer, db.ForeignKey('media.id'), primary_key=True),
                  db.Column('playlist_id', db.Integer, db.ForeignKey('playlist.id'), primary_key=True),
                  )

class Playlist(db.Model):
    __tablename__ = 'playlist'
    id = db.Column(db.Integer, primary_key=True)
    medias = db.relationship('Media',
                             secondary=medias,
                             lazy='subquery',
                             backref=db.backref('playlists', lazy=True))

The linked error :

UNIQUE constraint failed: media_playlist.media_id, media_playlist.playlist_id

How would you do to store several times the same link ?

Thank you, Rom

Answer 1

Why are you setting primary_key=True for foreign key columns? SQLAlchemy Since you are making a column foreign key it means it is going to store multiple times the same value, but when you make it primary key it means it needs to be unique and now you have this error fails UNIQUE constraint. If you want it to be indexed, you do not have to do anything as DB is indexing foreign keys by default.