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

PythonSqlalchemyAlembic

Python Problem Overview

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

Python Solutions

Solution 1 - Python

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)

Solution 2 - Python

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 :-).

Solution 3 - Python

It tells you - rightly - that there are (or will be) existing NULL values in the database for that column. The answer is to edit the migration file to update the column before changing the column definition:

from sqlalchemy.sql import table, column

def upgrade():
    op.add_column('role', sa.Column('role_name', sa.String(length=30), nullable=True))
    role = table('role', column('role_name'))       
    op.execute(role.update().values(role_name=''))       
    op.alter_column('role', 'role_name', nullable=False)
Categories
Recommended Python Solutions
Recommended Sqlalchemy Solutions
Recommended Alembic Solutions

Previous Solution:

How to restart kubernetes nodes?

NodesKubernetes

Next Solution:

How do you execute a query in Sequel Pro?

MysqlSequelpro

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionRonView Question on Stackoverflow
Solution 1 - PythonRonView Answer on Stackoverflow
Solution 2 - PythonAntoine LizéeView Answer on Stackoverflow
Solution 3 - PythonArjunsinghView Answer on Stackoverflow