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?

I would amend migration file..add column in nullable form, update with what user_id should be if null, then set nullable false. — Andrew Allen, Oct 16 '19 at 05:02

score 0 · Answer 1 · answered Mar 24 '20 at 07:40

A typical error message would be something like the following.

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

A workaround would be to set foreign key IDs to 0 for existing records, then treat them as None in your application code. The upgrade in the migration would look like this, where you have two tables team and user, and want to set a default value of team_id in the user record:

    team = op.create_table(
        "team",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.Text(), nullable=False),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_team")),
    )
    op.bulk_insert(
        team,
        [
            {"id": 0, "name": "Not Set"},
            {"id": 1, "name": "Team 1"},
            # ...
            {"id": N, "name": "Team N"},
        ],
    )


    op.add_column(
        "user",
        sa.Column("team_id", sa.Integer(), nullable=False, server_default="0"),
    )
    op.alter_column("user", "team_id", server_default=None)
    op.create_foreign_key(
        op.f("fk_team_id_user"),
        "user",
        "team",
        ["team_id"],
        ["id"],
        onupdate="CASCADE",
        ondelete="CASCADE",
    )

I might have got the child and parent swapped around, but this should give y'all something to try.

score 0 · Answer 2 · answered Feb 19 '21 at 23:52

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.

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

2 Answers2