id summary reporter owner description type status component version severity resolution keywords cc stage has_patch needs_docs needs_tests needs_better_patch easy ui_ux 29496 Altering non-unique field to primary key fails on Oracle Peter Levi Mariusz Felisiak "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: {{{#!python 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 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: {{{#!python 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: {{{#!python 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) }}} " Bug closed Database layer (models, ORM) 2.0 Normal fixed orm, oracle, alterfield, primary_key Mariusz Felisiak Ready for checkin 1 0 0 0 0 0