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

score 127 · Answer 1 · edited Aug 14 '18 at 13:50

127

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)

edited Aug 14 '18 at 13:50

dukebody

7,025
3
36
61

answered Nov 14 '15 at 06:13

Ron

7,588
11
38
42

31

@Steve use `'True'` and `'False'` instead of `True` and `False` – fujianjin6471 Apr 13 '18 at 15:22
11

It is also important to note that all values should be passed as string (even for integers!) since server_default is specifically the SQL text that one would specify in the "DEFAULT" section of a "CREATE TABLE" statement. In my case, I had to specify `op.add_column( "project", sa.Column("tenant_id", sa.Integer(), nullable=False, server_default="1"), )` to make it work (my id are integer) – Jean-Michel Provencher May 15 '19 at 15:27
3

In case of default values for `bool` type use `sa.sql.expression.false()` or `sa.sql.expression.true()` as follows: `op.add_column('table', sa.Column('col_name', sa.Boolean(), nullable=False, server_default=sa.sql.expression.false()))` – Tomasz Hławiczka Jul 21 '21 at 13:14
To add to this answer if you also want to add a column that's unique you can do it with these two lines: `op.execute("UPDATE mytable SET mycolumn = 'lorem ipsum-' || id")` `op.create_unique_constraint('mycolumn_key', 'mytable', ['mycolumn'])` and drop it with `op.drop_constraint('mycolumn_key', 'mytable', type_='unique')` – barryodev Sep 06 '22 at 13:56

score 46 · Answer 2 · answered Dec 07 '16 at 20:05

46

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

answered Dec 07 '16 at 20:05

Antoine Lizée

3,743
1
26
36

4

when using MySQL backend, last line should be op.alter_column('my_table', 'my_column', existing_type=sa.String(), nullable=False), otherwise you'll get an alembic.util.exc.CommandError (more about that [here](http://alembic.zzzcomputing.com/en/latest/ops.html#alembic.operations.Operations.alter_column)) – Maciej Kozik Mar 02 '18 at 20:11
Isn't this kinda dependant on the size of the table you're running the UPDATE on? Imagine a production table with 1M records... :/ – aqueiros Sep 08 '21 at 13:45
@Arjunsingh's answer does the same with SQLAlchemy objects rather than SQL. – Jérôme May 05 '23 at 07:51

score 21 · Answer 3 · answered Dec 10 '18 at 04:59

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)

This is a much cleaner way to do it vs. the other answers. – tzazo Jul 20 '20 at 20:27 — tzazo, Jul 20 '20 at 20:27

score 1 · Answer 4 · answered Jun 02 '23 at 19:16

Reflecting on Ron's comment, If you're using the class inheriting db.Model, you can add this (server_default parameter) to the model itself without changing the migration script. Specifically, the is_deleted and created_at columns are using it:

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.Text, nullable=True)
    last_name = db.Column(db.Text, nullable=True)
    email = db.Column(db.Text, unique=True, nullable=False, index=True)

    is_deleted = db.Column(db.Boolean, default=False, server_default=false(), nullable=False)
    created_at = db.Column(TIMESTAMP(timezone=True), default=datetime.now(tz=timezone.utc), server_default=db.func.now(), nullable=False)

    @classmethod
    def query(cls):
        return super(User, cls).query.filter_by(is_deleted=False)

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

4 Answers4