Altering non-unique field to primary key fails on Oracle

[Peter Levi]

I am trying to migrate a table from an auto-incrementing Integer PK to a UUID PK.
I have custom-written migrations that add the new UUID field, add new foreign key fields in dependent tables, populate them, remove the old FKs, etc.
One of the final steps of the process is to alter the new uuid field in the table to be a primary_key:

        migrations.AlterField(
            model_name='examplelayout',
            name='uuid',
            field=models.UUIDField(default=uuid.uuid4, primary_key=True, editable=False),
        ),

The whole process works great on Postgres, but this specific step ends up in an error on Oracle 12, despite the fact that before this step there are no indexes in the DB on this field.

query = 'ALTER TABLE "VARIO_EXAMPLELAYOUT" ADD CONSTRAINT "VARIO_EXA_UUID_8498C9C8_P" PRIMARY KEY ("UUID")'
...
django.db.utils.DatabaseError: ORA-02261: such unique or primary key already exists in the table

The issue seems to be that Django tries to first add a UNIQUE constraint for the field, then a PRIMARY KEY on the same field. Here are all the SQL queries executed by this specific migration step:

==================== STEP =========================  4  =====================


[2018-06-15 08:51:11.141 | DEBUG | django.db.backends.schema:124] ALTER TABLE "VARIO_EXAMPLELAYOUT" MODIFY "UUID" DEFAULT '9cf3fe16f11d4fa09b62e337c50ae5f7'; (params [])
[2018-06-15 08:51:11.212 | DEBUG | django.db.backends:111] (0.071) QUERY = 'ALTER TABLE "VARIO_EXAMPLELAYOUT" MODIFY "UUID" DEFAULT \'9cf3fe16f11d4fa09b62e337c50ae5f7\'' - PARAMS = (); args=[]
[2018-06-15 08:51:11.212 | DEBUG | django.db.backends.schema:124] UPDATE "VARIO_EXAMPLELAYOUT" SET "UUID" = %s WHERE "UUID" IS NULL; (params ['347ac08714bd4c208ffd2f79e7932bb3'])
[2018-06-15 08:51:11.269 | DEBUG | django.db.backends:111] (0.056) QUERY = 'UPDATE "VARIO_EXAMPLELAYOUT" SET "UUID" = :arg0 WHERE "UUID" IS NULL' - PARAMS = ('347ac08714bd4c208ffd2f79e7932bb3',); args=['347ac08714bd4c208ffd2f79e7932bb3']
[2018-06-15 08:51:11.269 | DEBUG | django.db.backends.schema:124] ALTER TABLE "VARIO_EXAMPLELAYOUT" MODIFY "UUID" NOT NULL; (params [])
[2018-06-15 08:51:11.343 | DEBUG | django.db.backends:111] (0.073) QUERY = 'ALTER TABLE "VARIO_EXAMPLELAYOUT" MODIFY "UUID" NOT NULL' - PARAMS = (); args=[]
[2018-06-15 08:51:11.343 | DEBUG | django.db.backends.schema:124] ALTER TABLE "VARIO_EXAMPLELAYOUT" ADD CONSTRAINT vario_exa_uuid_8498c9c8_u UNIQUE ("UUID"); (params ())
[2018-06-15 08:51:11.437 | DEBUG | django.db.backends:111] (0.094) QUERY = 'ALTER TABLE "VARIO_EXAMPLELAYOUT" ADD CONSTRAINT vario_exa_uuid_8498c9c8_u UNIQUE ("UUID")' - PARAMS = (); args=()
[2018-06-15 08:51:11.792 | DEBUG | django.db.backends:111] (0.354) QUERY = "\n            SELECT\n                user_constraints.constraint_name,\n                LISTAGG(LOWER(cols.column_name), ',') WITHIN GROUP (ORDER BY cols.position),\n                CASE user_constraints.constraint_type\n                    WHEN 'P' THEN 1\n                    ELSE 0\n                END AS is_primary_key,\n                CASE\n                    WHEN user_constraints.constraint_type IN ('P', 'U') THEN 1\n                    ELSE 0\n                END AS is_unique,\n                CASE user_constraints.constraint_type\n                    WHEN 'C' THEN 1\n                    ELSE 0\n                END AS is_check_constraint\n            FROM\n                user_constraints\n            LEFT OUTER JOIN\n                user_cons_columns cols ON user_constraints.constraint_name = cols.constraint_name\n            WHERE\n                user_constraints.constraint_type = ANY('P', 'U', 'C')\n                AND user_constraints.table_name = UPPER(:arg0)\n            GROUP BY user_constraints.constraint_name, user_constraints.constraint_type\n        " - PARAMS = ('vario_examplelayout',); args=['vario_examplelayout']
[2018-06-15 08:51:12.480 | DEBUG | django.db.backends:111] (0.688) QUERY = "\n            SELECT\n                cons.constraint_name,\n                LISTAGG(LOWER(cols.column_name), ',') WITHIN GROUP (ORDER BY cols.position),\n                LOWER(rcols.table_name),\n                LOWER(rcols.column_name)\n            FROM\n                user_constraints cons\n            INNER JOIN\n                user_cons_columns rcols ON rcols.constraint_name = cons.r_constraint_name AND rcols.position = 1\n            LEFT OUTER JOIN\n                user_cons_columns cols ON cons.constraint_name = cols.constraint_name\n            WHERE\n                cons.constraint_type = 'R' AND\n                cons.table_name = UPPER(:arg0)\n            GROUP BY cons.constraint_name, rcols.table_name, rcols.column_name\n        " - PARAMS = ('vario_examplelayout',); args=['vario_examplelayout']
[2018-06-15 08:51:13.695 | DEBUG | django.db.backends:111] (1.214) QUERY = "\n            SELECT\n                ind.index_name,\n                LOWER(ind.index_type),\n                LISTAGG(LOWER(cols.column_name), ',') WITHIN GROUP (ORDER BY cols.column_position),\n                LISTAGG(cols.descend, ',') WITHIN GROUP (ORDER BY cols.column_position)\n            FROM\n                user_ind_columns cols, user_indexes ind\n            WHERE\n                cols.table_name = UPPER(:arg0) AND\n                NOT EXISTS (\n                    SELECT 1\n                    FROM user_constraints cons\n                    WHERE ind.index_name = cons.index_name\n                ) AND cols.index_name = ind.index_name\n            GROUP BY ind.index_name, ind.index_type\n        " - PARAMS = ('vario_examplelayout',); args=['vario_examplelayout']
[2018-06-15 08:51:13.696 | DEBUG | django.db.backends.schema:124] ALTER TABLE "VARIO_EXAMPLELAYOUT" ADD CONSTRAINT "VARIO_EXA_UUID_8498C9C8_P" PRIMARY KEY ("UUID"); (params ())
[2018-06-15 08:51:13.775 | DEBUG | django.db.backends:111] (0.079) QUERY = 'ALTER TABLE "VARIO_EXAMPLELAYOUT" ADD CONSTRAINT "VARIO_EXA_UUID_8498C9C8_P" PRIMARY KEY ("UUID")' - PARAMS = (); args=()
Traceback (most recent call last):
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/oracle/base.py", line 500, in execute
    return self.cursor.execute(query, self._param_generator(params))
cx_Oracle.DatabaseError: ORA-02261: such unique or primary key already exists in the table

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "./manage.py", line 35, in <module>
    execute_from_command_line(sys.argv)
  File "/var/www/venv/lib/python3.5/site-packages/django/core/management/__init__.py", line 371, in execute_from_command_line
    utility.execute()
  File "/var/www/venv/lib/python3.5/site-packages/django/core/management/__init__.py", line 365, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/var/www/venv/lib/python3.5/site-packages/django/core/management/base.py", line 288, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/var/www/venv/lib/python3.5/site-packages/django/core/management/base.py", line 335, in execute
    output = self.handle(*args, **options)
  File "/var/www/forms/forms/common/management/commands/migrate.py", line 54, in handle
    super().handle(*args, **options)
  File "/var/www/venv/lib/python3.5/site-packages/django/core/management/commands/migrate.py", line 200, in handle
    fake_initial=fake_initial,
  File "/var/www/venv/lib/python3.5/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/migrations/migration.py", line 122, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/migrations/operations/fields.py", line 216, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/oracle/schema.py", line 59, in alter_field
    super().alter_field(model, old_field, new_field, strict)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 525, in alter_field
    old_db_params, new_db_params, strict)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 725, in _alter_field
    "columns": self.quote_name(new_field.column),
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 133, in execute
    cursor.execute(sql, params)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 100, in execute
    return super().execute(sql, params)
  File "/var/www/venv/lib/python3.5/site-packages/raven/contrib/django/client.py", line 127, in execute
    return real_execute(self, sql, params)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/var/www/forms/forms/forms/db/backends/utils.py", line 16, in execute
    return super().execute(sql, params)
  File "/var/www/venv/lib/python3.5/site-packages/raven/contrib/django/client.py", line 127, in execute
    return real_execute(self, sql, params)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/var/www/venv/lib/python3.5/site-packages/django/db/backends/oracle/base.py", line 500, in execute
    return self.cursor.execute(query, self._param_generator(params))
django.db.utils.DatabaseError: ORA-02261: such unique or primary key already exists in the table

Notice the two calls:

[2018-06-15 08:51:11.437 | DEBUG | django.db.backends:111] (0.094) QUERY = 'ALTER TABLE "VARIO_EXAMPLELAYOUT" ADD CONSTRAINT vario_exa_uuid_8498c9c8_u UNIQUE ("UUID")' - PARAMS = (); args=()

and a bit below:

[2018-06-15 08:51:13.775 | DEBUG | django.db.backends:111] (0.079) QUERY = 'ALTER TABLE "VARIO_EXAMPLELAYOUT" ADD CONSTRAINT "VARIO_EXA_UUID_8498C9C8_P" PRIMARY KEY ("UUID")' - PARAMS = (); args=()
...which raises the error

The only other operations before this on this field that I have in one of the previous migrations is to actually add this UUID field as a nullable, non-unique field, and then in a data migration to populate it with a unique uuids for all ExampleLayouts:

        # prepare to migrate to UUID PKs for ExampleLayout and LayoutField
        migrations.AddField(
            model_name='examplelayout',
            name='uuid',
            field=models.UUIDField(null=True, default=uuid.uuid4),
        ),

For completeness, the initial models look like this:

class ExampleLayout(models.Model):
    image = models.OneToOneField(
        Image, on_delete=models.CASCADE, related_name='example_layout')
    doc_type = models.ForeignKey(
        DocumentType, on_delete=models.CASCADE, related_name='layouts')


class LayoutField(models.Model):
    core_field_type = models.ForeignKey(
        CoreFieldType, on_delete=models.CASCADE, related_name='layout_fields')
    layout = models.ForeignKey(
        ExampleLayout, on_delete=models.CASCADE, related_name='fields')
    bounding_box = BoundingBoxCharField()
    transcription = models.CharField(max_length=128)

while their final "target" version that I am trying to migrate to is this:

class ExampleLayout(models.Model):
    uuid = models.UUIDField(
        default=uuid.uuid4, primary_key=True, editable=False)
    image = models.OneToOneField(
        Image, on_delete=models.CASCADE, related_name='example_layout')
    doc_type = models.ForeignKey(
        DocumentType, on_delete=models.CASCADE, related_name='layouts')


class LayoutField(models.Model):
    uuid = models.UUIDField(
        default=uuid.uuid4, primary_key=True, editable=False)
    core_field_type = models.ForeignKey(
        CoreFieldType, on_delete=models.CASCADE, related_name='layout_fields')
    layout = models.ForeignKey(
        ExampleLayout, on_delete=models.CASCADE, related_name='fields')
    bounding_box = BoundingBoxCharField()
    transcription = models.CharField(max_length=128)

[Peter Levi]

migration #1 - schema changes to add new uuid fields

[Peter Levi]

migration 2 - data changes to populate the new uuid fields and references

[Peter Levi]

migration 3 - schema changes to remove old ID fields, rename temporary FKs back to the original names, and introduce the primary_keys. Fails on step 4.

[Tim Graham]

Can you provide a minimal project that reproduces the issue (or ideally, a test for Django's test suite, which would probably go in tests/schema)?

[Peter Levi]

Minimal project demoing the bug, including the migrate failure log that I observe

[Peter Levi]

Added a minimal project which exhibits the issue. Single model, single field in it. We start with an auto-created PK, then we try to make the single UUID field to be the PK.
The migration was auto-generated with makemigrations, then edited to show individual steps in the log. log from the failing migrate call included as well.

Thanks and regards,
Peter

[Mariusz Felisiak]

Test.

[Mariusz Felisiak]

I reproduced this issue and prepared test in our test suite.

[Mariusz Felisiak]

Oracle creates UNIQUE constraints implicite when we add PRIMARY KEY constraints, moreover it is not so clever to match them if they already exist, that's why it throws: ORA-02261: such unique or primary key already exists in the table.

​PR

[GitHub <noreply@…>]

In 6dd4edb1:

Fixed #29496 -- Fixed crash on Oracle when converting a non-unique field to primary key.

Thanks Tim Graham for the review.

[Mariusz Felisiak <felisiak.mariusz@…>]

In d2ca28db:

[2.1.x] Fixed #29496 -- Fixed crash on Oracle when converting a non-unique field to primary key.

Thanks Tim Graham for the review.
Backport of 6dd4edb1b4f5441c5f543e29395039839c50d10b from master