Relative Primary Key in Flask_SQLAlchemy

Question

Context/Minimal Example: I'm relatively new to Database design and trying to design a small app in Flask/Flask_SQLAlchemy that tracks inventory.

I have a User table:

class Users(db.Model):
    user_id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(25))
    items = db.relationship('Item', lazy="dynamic")

and a Item table:

class Item(db.Model):
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), index=True)
    sku = db.Column(db.Integer, index=True, autoincrement=True)
    name = db.String(10)

I would like to create a system where the Item.sku is unique... but only based on the user.id.
E.g. two users can have items of the same Sku, but one user may not have multiple items of the same sku. (And preferably have the sku automatically increment itself).

To me, this is a constraint that makes sense- sku+user_id should always be a unique combination, so I can save space and simplicity by using it as a primary key, as well as increasing the ?normalization? of the database.

However, I've spent a fair amount of time now reading and trying to figure out how to do this and I keep running into problems. Is there an easy way of accomplishing this, or is there something wrong with my logic that has lead to this design? Are there downsides to this I'm missing?

So far I've tried:

Setting both user_id and sku to primary_key=true
Setting them both to index=True (as you can see here)
Adding a table_args = db.PrimaryKeyConstraint (As discussed here https://www.reddit.com/r/flask/comments/g3tje5/composite_key_for_flasksqlalchemy/)

From what I've read the term of what I'm trying to accomplish here is a compound primary key, and that flask_sqlalchemy does support it, but with all of these I get exceptions that a constraint is failing or a parameter is missing.

Thanks for any help or advice you can provide.

score 0 · Answer 1 · answered May 08 '22 at 16:34

Yes, a composite PK on (user_id, sku) will work, as in this example using vanilla SQLAlchemy ORM:

import sqlalchemy as db
from sqlalchemy.orm import declarative_base, relationship, Session

engine = db.create_engine("sqlite://")

Base = declarative_base()


class Users(Base):
    __tablename__ = "users"
    user_id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(25))
    items = relationship('Item', lazy="dynamic")


class Item(Base):
    __tablename__ = "item"
    user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), primary_key=True)
    sku = db.Column(db.Integer, index=True, primary_key=True)
    name = db.String(10)


Base.metadata.create_all(engine)

with Session(engine) as sess:
    gord = Users(first_name="Gord", items=[Item(sku=1)])
    anne = Users(first_name="Anne", items=[Item(sku=1), Item(sku=2)])
    sess.add_all([gord, anne])
    sess.commit()
    # okay so far

    # now try to add a duplicate
    gord.items.append(Item(sku=1))
    sess.flush()
    """
    sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: item.user_id, item.sku
    [SQL: INSERT INTO item (user_id, sku) VALUES (?, ?)]
    [parameters: (1, 1)]
    """

Relative Primary Key in Flask_SQLAlchemy

1 Answers1