Changing a PositiveIntegerField to an IntegerField does not remove >=0 check in migration

[Jared Proffitt]

When you change a PositiveIntegerField to just an IntegerField, the >=0 database check in postgres is not removed when running the migration.

Initial migration:

('year', models.PositiveIntegerField(blank=True, null=True,)),

Alter migration:

migrations.AlterField(
    model_name='art',
    name='year',
    field=models.IntegerField(blank=True, null=True),
    preserve_default=True,
),

I'm left with having to do a RunSQL operation to remove the constraint.

[Simon Charette]

I cannot reproduce with the latest 1.7.x release (1.7.9) on PostgreSQL.

Can you provide more details?

[Jared Proffitt]

Sorry, I never knew someone responded. I am still having this issue. I am using django 1.8.4 now. Here are the steps to reproduce:

1. Create a model with a PositiveIntegerField:

class MyModel(models.Model):
    an_integer = models.PositiveIntegerField(null=True, blank=True)

2: create migrations, and you get this migration:

class Migration(migrations.Migration):

    dependencies = [
    ]

    operations = [
        migrations.CreateModel(
            name='MyModel',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
                ('an_integer', models.PositiveIntegerField(null=True, blank=True)),
            ],
        ),
    ]

which generates this constraint in Postgres:

ALTER TABLE myproject.myapp_mymodel
  ADD CONSTRAINT myapp_mymodel_an_integer_check CHECK (an_integer >= 0);

3. Change the PositiveIntegerField to a plain IntegerField:

class MyModel(models.Model):
    an_integer = models.IntegerField(null=True, blank=True)

4. Make migrations again, and you get this migration:

class Migration(migrations.Migration):

    dependencies = [
        ('myapp', '0001_initial'),
    ]

    operations = [
        migrations.AlterField(
            model_name='mymodel',
            name='an_integer',
            field=models.IntegerField(null=True, blank=True),
        ),
    ]

But the constraint is still there in the database. To check, I can try to create a record with a negative integer:

>>> from myapp.models import MyModel
>>> MyModel.objects.create(an_integer=-5)

...
IntegrityError: new row for relation "myapp_mymodel" violates check constraint "myapp_mymodel_an_integer_check"

[Simon Charette]

Hi jproffitt,

I still can't reproduce on Python 2.7 with Django 1.8.4.

The constraint is effectively dropped if I run your exact testcase in isolation. Here's the output of sqlmigrate for the two migrations.

0001_initial

BEGIN;
CREATE TABLE "ticket_25133_foo" ("id" serial NOT NULL PRIMARY KEY, "num" integer NULL CHECK ("num" >= 0));

COMMIT;

0002_auto_...

BEGIN;
ALTER TABLE "ticket_25133_foo" DROP CONSTRAINT "ticket_25133_foo_num_6f290808d3aecee4_check";

COMMIT;

What does sqlmigrate yield for your second migration?

[Jared Proffitt]

That is strange. I guess its something I am doing wrong then. sqlmigrate for 0002 yields nothing for me. I am using a custom SCHEMA in postgres if that makes any difference.

Here are the relevant versions I am using:

python 2.7.9
django 1.8.4
psycopg2 2.6.1

[Simon Charette]

Custom SCHEMA are not yet supported so it makes a big difference, see #6148 which you should be aware of at this point.

If I remember correctly constraints retrieval is only done against the public schema which might be why you're having trouble here.

[Jared Proffitt]

Oh wow. Thats probably it then. I didn't know that wasn't supported. We've been using custom SCHEMAs for years. I've never noticed any issues until now. I assumed that setting 'SCHEMA' in the DATABASES setting would just work. Now I can even see that that is not even documented!

[Simon Charette]

I never knew this option even existed, I assumed you were setting a search_path on ​connection_created.

After ​verification It looks like were really just retrieving constraints defined in the public schema.

[Jared Proffitt]

Well it turns out that option doesn't even exist! I guess it was just wishful thinking on our part. Would be nice if that was supported though.