SQLAlchemy: UNIQUE constraint failed error, yet no unique constraints set

Question

I am using Flask and SQLAlchemy with SQLite to create an app that keeps track of items in your pantry as well as recipes that you like to make.

I'm stumped on this one. I have a table in which it should be possible for records to have duplicate fields. Every time I attempt to make a database entry for this table in my app, an "IntegrityError: UNIQUE constraint failed" gets raised. This is baffling because the column it's getting raised on does not have a unique constraint explicitly set.

Here's the model for the table I am inserting into:

class PantryItem(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    amount = db.Column(db.String(12))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    def __repr__(self):
        return '<PantryItem %r> % self.name'

Here is the view code for the entry that should be added to the databse:

@app.route('/pantry', methods=['GET', 'POST'])
@login_required
def pantry():

    form = IngredientForm() 
    pantry = PantryItem.query.filter_by(owner=g.user).all()

    if form.validate_on_submit():

        pantry_item = PantryItem(name=form.name.data, amount=form.amount.data, owner=g.user)
        db.session.add(pantry_item)
        db.session.commit()

        flash('You added %s to the pantry.' % pantry_item.name)
        return redirect(url_for('pantry'))

    return render_template('pantry.html', 
               form=form,
               user=g.user,
               pantry=pantry)

And here is the error I'm getting on form submission:

IntegrityError: (IntegrityError) UNIQUE constraint failed: pantry_item.name u'INSERT INTO pantry_item (name, amount, user_id) VALUES (?,  ?, ?)' (u'Milk', u'2 cups', 1)

There is already a record in the table for a PantryItem with the name "Milk".

Am I incorrect in assuming that PantryItem's primary key "id" will differentiate a record from another record with the same name/amount/owner? Or am I just completely overlooking something?

Thank you in advance for your help.

Answer 1

Drop your tables or delete your DB and initialize it again. Every time you make a schema change you must drop your tables and re-initialize, it doesn't just update.