Alembic: IntegrityError: "column contains null values" when adding non-nullable column

Question

I'm adding a column to an existing table. This new column is nullable=False.

op.add_column('mytable', sa.Column('mycolumn', sa.String(), nullable=False))

When I run the migration, it complains:

sqlalchemy.exc.IntegrityError: column "mycolumn" contains null values

Answer 1

It is because your existing data have no value on that new column, i.e. null. Thus causing said error. When adding a non-nullable column, you must decide what value to give to already-existing data

---

Alright, existing data should just have "lorem ipsum" for this new column then. But how do I do it? I can't UPDATE because the column is not there yet.

Use the server_default arg:

op.add_column('mytable', sa.Column(     'mycolumn',      sa.String(),      nullable=False,      server_default='lorem ipsum', #  <---  add this )) 

---

But, but, I don't want it to have default value

Drop it afterwards using op.alter_column('mytable', 'mycolumn', server_default=None)

E.g. your upgrade() function would be:

def upgrade():     op.add_column('mytable', sa.Column('mycolumn', sa.String(), nullable=False, server_default='lorem ipsum'))     op.alter_column('mytable', 'mycolumn', server_default=None) 

Answer 2

An alternative to @Ron's answer is to do the contrary, and modify the data before adding the constraint:

def upgrade():
    op.add_column('my_table', sa.Column('my_column', sa.String()))
    op.execute('UPDATE my_table SET my_column=my_other_column')
    op.alter_column('my_table', 'my_column', nullable=False)

Seems cleaner and more powerful to me, but you're writing SQL :-).