Creating a non null column with default value set causes integrity error
Felix Schwarz
I just started with sqlalchemy-migrate so please don't assume that this is a
not a kind of stupid PEBKAC error. :-)
I'm trying to create a non-null column (with a default value) as part of my
migration script. However creating that column leads to an OperationalError.
It looks to me as if migrate generates SQL without default value:
"sqlalchemy.exc.OperationalError: (OperationalError) Cannot add a NOT NULL
column with default value NULL '\nALTER TABLE "foo" ADD is_active BOOLEAN NOT
NULL' ()"
Is there something obviously wrong?
fs
Here's the script I'm using:
-----------------------------------------------------------------------------
from sqlalchemy import *
from migrate import *
from migrate.changeset import create_column, drop_column
metadata = MetaData(migrate_engine)
is_active = Column('is_active', Boolean(), nullable=False, default=False)
foo_table = Table('foo', metadata,
Column('id', Integer(), primary_key=True, nullable=False),
Column('key', Integer(), nullable=False),
)
def upgrade():
is_active.create(table=foo_table)
def downgrade():
drop_column(is_active)
-----------------------------------------------------------------------------
Felix Schwarz
I just wanted to mention that I found a temporary workaround using
server_default='false' - however I'm a bit confused why this is necessary...
If I have to use this everywhere, I can not use my model definition straight
away (with copy&paste) in my upgrade scripts...
fs
Felix Schwarz
sorry for the spamming - even after checking the code, I think this is a
bug/missing feature - in migrate/changeset/ansisql.py there is an explicit
comment:
"""
if 'server_default' in keys:
# Skip 'default': only handle server-side defaults, others
# are managed by the app, not the db.
self._run_subvisit(delta, self._visit_column_default)
"""
However this is not completely true. If I create the column with a simple
default value (e.g. False), this *is* handled by the db and table.create will
produce a proper column definition in that case.
fs
Domen Kožar
database backend.
Try using server_default=False
In any case, you should read sqlalchemy documentation how defaults are
handled.
> 4KViewDownload
Felix Schwarz
> default argument is python object, while server_default is handled by
> database backend.
>
> Try using server_default=False
Actually I'm using 0.4.5 - and AFAIK server_default was added only in 0.5.
Even after re-reading the docs, I have some questions to these some (I
believe) common use cases. If someone can shed some light for me, this would
be great. Maybe my situation is just over-complicated by the fact that I use
Elixir on-top [1]...
If I declare a table/elixir Entity (SQLAlchemy 0.4), the value from default is
(sometimes) taken for the sql generation in CREATE TABLE. This doesn't work
like that if I add a column with a default value as pointed out by Domen.
1) How can I add a server default (even in 0.5) without specifying a string?
Does PassiveDefault(True)/PassiveDefault(datetime.today()) work?
2) Assume the new, non-nullable column should store a foreign key. However the
referenced table should be created in the same upgrade script. I try
explain the situation a bit better with pseudo-code:
enum_table.create()
result = enum_table.insert(values=dict(...)).execute()
status = Column('status_id', Integer, ForeignKey('enum_table.id'),
nullable=False, server_default=???)
What value should I use for server_default in that situation? The primary
key is a numeric id which I don't know at that moment...
Is this more appropriate in the Elixir list because Elixir does so many
special things?
Any help really appreciated :-)
fs
Domen Kožar
ot stuff, but I'm sure that also broke 0.4.5 compability.
Current trunk only supports 0.5.x and 0.6.x branches.
1) migrate operates on server_defaults exactly the same way as SA,
figure out how to do it in normal create table statement
2) I think this more general to SQL databases, I'm not familiar much
with solutions, but I agree migrate should solve also those pitfals
> 4KViewDownload
Felix Schwarz
> Hm, what migrate version are you using?
The version I would like to use is SQLAlchemy 0.4.x with migrate 0.4.x. For
production app I can not switch to a newer SQLAlchemy easily and I was under
the impression that migrate 0.5 only supports SQLAlchemy 0.5.
For some simple tests I installed the 0.5 versions of both libraries on my
development machine but even this did not help fixing my problems...
fs
Domen Kožar
will apply it. Source code is not very complicated, you have to dive
into changeset/ansiql.py
> 4KViewDownload