Flask - SQLAlchemy - Add nullable=False column to existing table with data

Question

I'm trying to add a new nullable=False column to an existing table. But it won't create the column because there are existing rows and the field is Null.

It's a classic catch 22 :D.

here's my model column I added:

user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)

here's the error I get

sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) column "user_id" contains null values

I tried adding , default=0 to the end of the attribute in the model hoping the migration would just set all existing row's column values to 0, but this didn't have any effect.

I don't see any documentation around this seemingly common phenomenon so I thought I'd ask the experts. What am I missing?

Answer 1

I was having the same problem as you. Please see this comment from another thread. Essentially, the "default" argument in the Column definition is only used by the class instance. If you'd like to set a default that the migration can interpret, use the "server_default" argument with a SQL expression instead.