Flask-sqlalchemy unique contraint not working

Question

I'm trying to figure out why the "unique" constraint of a field is not working. I'm using Flask, SQLAlchemy, and Sqlite.

Consider the model:

from app import db
from flask_login import UserMixin

class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), unique=True)
    password = db.Column(db.String(64))
    email = db.Column(db.String(120), unique=True)

    def __repr__(self):
        return '<User %r>' % self.username

I would expect, since there is a unique constraint on the username field, that it wouldn't allow me to add users with the same username. It does, though.

From the shell:

from app import db
from app.models import User

user = User(username='test')
user2 = User(username='test')

db.session.add(user)
db.session.commit()

db.session.add(user2)
db.session.commit()

User.query.all()

Output:

[<User 'test'>, <User 'test'>]

Am I missing something?

Answer 1

Have you added the unique = True after creating the tables the first time? If so, the constraint will not be applied to the database. How you need to proceed with this is as follows:

1. Use Flask Migrations. This is a very useful to that allows you to migrate existing database models. (Recommended)

2. If this is a test/dev environment, just drop the tables and run db.create_all() again.

I was not able to replicate the error you posted by just creating the database with the unique constraint added before creating the table.

However, I was able to do that by adding the unique constraint after creating the table.

Hope this helps!