Flask-Migrate: sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) Cannot add a NOT NULL column with default value NULL

After successful $python application.py db init $python application.py db migrate

But upon $python application.py db upgrade

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) Cannot add a NOT NULL column with default value NULL [SQL: u’ALTER TABLE user ADD COLUMN title VARCHAR(10) NOT NULL’]

In my database the new column is defined a ‘title’ as in:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(10), unique=False, nullable=False, info={'validators': InputRequired()})
    firstname = db.Column(db.String(30), unique=False, nullable=False, info={'validators': InputRequired()})
    middlename = db.Column(db.String(30), unique=False, nullable=True, info={'validators': Optional()})
    lastname = db.Column(db.String(30), unique=False, nullable=False, info={'validators': InputRequired()})
    email = db.Column(db.String(120), unique=True, nullable=False, info={'validators': Email()})
    password = db.Column(db.String(80), nullable=False)
    posts = db.relationship('Post', backref='user', lazy='dynamic')

    def __init__(self, title, firstname, middlename, lastname, email, password):
        self.title = title
        self.firstname = firstname
        self.middlename = middlename
        self.lastname = lastname
        self.email = email
        self.password = flask_bcrypt.generate_password_hash(password)

Or do I need to add attribute server_default or default to ‘title’ attribute? as previously defined data didn’t have it

I do see some solutions but these are all in SQL syntax, staying within Python-Migrate constraint would be preferable.

About this issue

Original URL
State: closed
Created 9 years ago
Comments: 21 (5 by maintainers)

Most upvoted comments

@mzord kind of defeats the purpose of using alembic/flask-migrate eh?

I`ve just deleted my migrations folder and run the flask db init, flask db migrate, flask db upgrade again and everything went fine!

graydenshand on Oct 17, 2018

I have the same problem. Did you find a solution? In my case the field must be nullable=False, but even with adding default=“” in both the model and migration script I still get the same error. Can you help please

smalekzadeh on Jan 12, 2017

@smalekzadeh your field cannot be nullable=False if you have existing columns that are NULL. Fix those columns first, then the migration will work.

miguelgrinberg on Jan 12, 2017

If you set nullable=True then things should work, your existing rows will have NULL as value for the new column.

Alternatively, you can set a default to an empty string, that should work too.

miguelgrinberg on Sep 11, 2015

I’m trying to add a new Boolean column to my Sqlite database using migration. But getting [SQL: ALTER TABLE post ADD COLUMN private BOOLEAN NOT NULL] error even though I have specified default=False.

class Post(db.Model):
	id = db.Column(db.Integer,primary_key=True)
	title = db.Column(db.String(100),nullable=False)
	date_posted = db.Column(db.DateTime,nullable=False,default=datetime.utcnow)
	content = db.Column(db.Text,nullable=False)
	user_id = db.Column(db.Integer, db.ForeignKey('user.id'),nullable=False)
	img = db.Column(db.String(20), nullable=True)
	private = db.Column(db.Boolean, nullable=False, default=False)

SahooAshis on Sep 24, 2021

What you actually need to do, if you encounter this error now like I did, ist to set the server_default value in your db.Column instead of the default value. This stackoverflow answer cleared this up for me. Apparently default does not do what you would think it does. Hope this helps anyone finding this issue before that stackoverflow page like me 😃

Mastermori on Oct 24, 2023

I had the same problem, but as Miguel suggested, I checked the migration code and I noticed although I have changed the nullable to True, but in migration script it was still False. I changed it to False manually in migration script, and it worked with the following warning:

UserWarning: Skipping unsupported ALTER for creation of implicit constraint
  "Skipping unsupported ALTER for "

And everything seems to be fine now.

aramhamidi on Jul 25, 2019

Try setting the server_default parameter in your migration script/model. default is handled by SQLAlchemy. server_default actually sets this in your DB

https://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.Column

graydenshand on Sep 26, 2018