SQLALCHEMY: There is no unique constraint matching given keys for referenced table

Question

I'm trying to create a relationship between my tables seller and item where each seller can sell any number of items but they can't sell the same item twice. Here's what I have:

sells = db.Table('sells', db.Column('seller_email', db.String(), db.ForeignKey('seller.email'), primary_key=True), db.Column('item_id', db.Integer, ForeignKey('item.id'), primary_key=True) ) class Item(db.Model): __tablename__ = 'item' id = db.Column(db.Integer, primary_key=True) coverPhoto = db.Column(db.String()) price = db.Column(db.Integer) condition = db.Column(db.Integer) title = db.Column(db.String()) def __init__(self, title, coverPhoto, price, condition): self.coverPhoto = coverPhoto self.price = price self.condition = condition self.title = title def __repr__(self): return '<id {}>'.format(self.id) class Seller(db.Model): __tablename__ = 'seller' email = db.Column(db.String(), primary_key=True) password = db.Column(db.String()) firstName = db.Column(db.String()) lastName = db.Column(db.String()) location = db.Column(db.String()) def __init__(self, email, password, firstName, lastName, location): self.email = email self.password = password self.firstName = firstName self.lastName = lastName self.location = location def __repr__(self): return "<Seller {email='%s'}>" % (self.email)

And I get the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) there is no unique constraint matching given keys for referenced table "seller" [SQL: '\nCREATE TABLE sells (\n\tseller_email VARCHAR NOT NULL, \n\titem_id INTEGER NOT NULL, \n\tPRIMARY KEY (seller_email, item_id), \n\tFOREIGN KEY(item_id) REFERENCES item (id), \n\tFOREIGN KEY(seller_email) REFERENCES seller (email)\n)\n\n']

Bother seller.email and item.id are primary keys so shouldn't they inherently be unique?

Yes, primary keys are inherently unique. I'm not knowledgeable about SQLAlchemy, but in an SQL database creation script, the definition of the relationship table would need to follow the definitions of the other two. Does it make a difference if you move the sells = part to the end of your script? — John Bollinger
– John Bollinger, Commented Jan 8, 2016 at 19:17

Jeff Widman · Accepted Answer · 2016-01-08 20:23:41Z

You're creating the table sells using db.Table, a SQLAlchemy Core function. Nothing wrong with that. Then you create your other tables by inheriting from db.Model using the SQLAlchemy ORM's declarative syntax. (If you're not familiar with the difference between SQLAlchemy Core and ORM, the tutorial is a good place to start.)

You've got a couple of potential issues here:

You're using db.Model and the SQLAlchemy ORM's declarative syntax. When you do this, your model subclasses don't need an __init__ function. In fact, using an __init__ likely will cause problems (possibly even the root cause here) as it will interfere with the monkey-patching that SQLAlchemy does to make the Declarative syntax so convenient...
I suspect that the root cause here might actually be that you use SQLAlchemy Core to create a table with a foreign key reference to a SQLAlchemy ORM-managed table. Normally, when you do db.metadata.create_all(), SQLAlchemy will collect all the table/model mappings, look at the dependencies and figure out the correct ordering for emitting the CREATE TABLE / ADD CONSTRAINT commands to the database. This under-the-covers dependency resolving is what allows the app programmer to define a table that includes a foreign key to a table that is defined later. When you have a Core-based Table object that references a ORM db.Model-based object, it might prevent the dependency resolving from working correctly during table creation. I'm not 100% confident this is the issue, but it's worth experimenting with making all your tables either Table objects, or db.Model subclasses. If you're not sure, I'd suggest the latter.

flask-sqlalchemy.pocoo.org/2.1/models/… Their documentation says otherwise. They suggest making the relation a table and everything else models.
It certainly can work, but it's a bit trickier to get everything configured correctly because you have to be more conscious of when you're using Core vs the ORM. Since you used an __init__ in your model class, it looks like you're not that familiar with SQLAlchemy's declarative syntax, so that's partially why I suggested standardizing on one construct until you're comfortable with it. Did you try it without the __init__ to see if that fixed the issue?
You're I'm not too familiar with SQLAlchemy. I followed this tutorial: realpython.com/blog/python/… and they had __init__ so I added it to mine as well. I actually got it working by copying the code from the link above.
So... was there a solution here or not? If so, what exactly was it? I see that this answer was accepted as correct but I don't see why. The rest of us could use a hint before going down the same rabbit holes.

Collectives™ on Stack Overflow

SQLALCHEMY: There is no unique constraint matching given keys for referenced table

1 Answer 1

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Related