default id field not migrated correctly

[Brian May]

In my db models, I had legacy id fields that looked like:

id = models.AutoField(primary_key=True)

The relevant migration was:

       migrations.AlterField(
           model_name='person',
           name='id',
           field=models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True),
           preserve_default=True,
       ),

Which resulted in the following sql:

ALTER TABLE "spud_person" ALTER COLUMN "id" DROP DEFAULT;

I removed these fields, and made a new migration, and ran it. Unfortunately, the id field no longer auto generates.

Exception Value: null value in column "id" violates not-null constraint

Non-working table:

spud=> \dS spud_person;
         user_url        | character varying(200)   | not null
     Table "public.spud_person"
     Column     |          Type          | Modifiers
----------------+------------------------+-----------
 id             | integer                | not null

For comparison here is a working table:

spud=> \dS spud_feedback
                                      Table "public.spud_feedback"
     Column      |           Type           |                         Modifiers
-----------------+--------------------------+------------------------------------------------------------
 id              | integer                  | not null default nextval('spud_feedback_id_seq'::regclass)

I suspect this is a postgres specific issue.

If my understanding is correct, when the table is created it is created as type "serial", which makes it integer with the nextval set, however it is not possible to set an existing table column to serial.

I believe something like the following is needed to fix the problem:

create sequence spud_person_id_seq;
ALTER TABLE "spud_person" ALTER COLUMN "id"  set DEFAULT nextval('spud_person_id_seq'::regclass);

[Brian May]

Actually just realized in my case, the correct command to fix this:

ALTER TABLE "spud_person" ALTER COLUMN "id"  set DEFAULT nextval('spud_person_person_id_seq'::regclass);

Which probably reflects the fact the column name was called "person_id" before I renamed it to "id", and the rename didn't rename the sequence name (is that another bug?)

[Brian May]

Thinking about this some more, the root problem is that the database migration drops the default value on the column.

ALTER TABLE "spud_person" ALTER COLUMN "id" DROP DEFAULT;

This in turn drops the auto-increment.

[Tim Graham]

#22997 seems related. See also #23581 for more cases where unnecessary DROP DEFAULT statements are issued.

[Brian May]

This appears to be fixed now in the stable/1.7.x tree.

[brawaga]

I ran into it on Django 1.8.6. This bug still remains.

[Tim Graham]

What are the steps to reproduce? Is it different from #22997?

[Tim Graham]

@brawaga, please open a new ticket with steps to reproduce the issue you are seeing. I followed the steps from the description and couldn't reproduce: sqlmigrate gave an empty output for the AlterField operation.

[Anupam]

Did a new ticket ever get opened for this one? I am seeing the same behaviour on Django 1.10.2.

I used a custom primary key and later decided to go back to Django's default primary key (by not specifying any). On running makemigrations, it asked for default value for auto-generated ID field (because of the issue discussed in #22997). Providing a default ID also doesn't help because on running the migration, it gives the error 'Multiple default values specified for column “modelname_id"' ' which is expected since its an auto-increment field. To get around that, it seems the only solution is as mentioned here ​http://stackoverflow.com/a/37356512/1526703 where the default is actually supplied at the prompt and then manually deleted from the migration created. After using the above workaround, the issue talked about in this ticket gets revealed (i.e the id field no longer auto-increments). Dropping the db and removing all migrations was the only thing that worked for me to get the default "id" field to auto-increment (otherwise keep getting the error 'null value in column "id" violates not-null constraint' on any insert).

[Tim Graham]

Is it different from #22997? Please give explicit step by step instructions (including code) to reproduce the issue.