title: Peewee migrations changing foreign key constraint
body: <p>I have a situation where I need to change the foreign key constraint to implement cascade delete, and write the migration steps for the change. I'm using Flask with peewee as an ORM tool and peewee_migrate as a tool for migrations.</p>
  
  <p>When creating a migration from a migrate template provided in the peewee_migrate router, a set of available methods is added to the migration.</p>
  
  <pre><code>"""Peewee migrations -- 007_Added_cascade_delete.py.
  
  Some examples (model - class or model name)::
  
      &gt; Model = migrator.orm['model_name']            # Return model in current state by name
  
      &gt; migrator.sql(sql)                             # Run custom SQL
      &gt; migrator.python(func, *args, **kwargs)        # Run python code
      &gt; migrator.create_model(Model)                  # Create a model (could be used as decorator)
      &gt; migrator.remove_model(model, cascade=True)    # Remove a model
      &gt; migrator.add_fields(model, **fields)          # Add fields to a model
      &gt; migrator.change_fields(model, **fields)       # Change fields
      &gt; migrator.remove_fields(model, *field_names, cascade=True)
      &gt; migrator.rename_field(model, old_field_name, new_field_name)
      &gt; migrator.rename_table(model, new_table_name)
      &gt; migrator.add_index(model, *col_names, unique=False)
      &gt; migrator.drop_index(model, *col_names)
      &gt; migrator.add_not_null(model, *field_names)
      &gt; migrator.drop_not_null(model, *field_names)
      &gt; migrator.add_default(model, field_name, default)
  
  """
  </code></pre>
  
  <p>I tried dropping the column with remove_fields method, and then adding a new foreign key column with on cascade delete with add_fields method, but unsuccessful because somehow the column isn't removed but no errors are shown, and that causes an error that the foreign key with that name already exists.</p>
  
  <p>When using change_fields method, I get an error that the drop_foreign_key_constraint is not implemented in the peewee's playhouse migrate because change_fields method tries to drop the foreign key before changing the field, and if the changed field is also a foreign key, tries to add a foreign key constraint with add_foreign_key_constraint method (also not implemented)</p>
  
  <pre><code>@operation
  def drop_foreign_key_constraint(self, table, column_name):
      raise NotImplementedError
  
  @operation
  def add_foreign_key_constraint(self, table, column_name, field):
      raise NotImplementedError
  </code></pre>
  
  <p>The only solution I came up with is by using the sql method which allows custom sql to execute, but the issue is that this could only work on a specific database because of the different syntax between postgresql, mysql and sqlite. Database used for development/production is postgres, and sqlite in-memory database is used for testing, which causes all tests to fail, but migrations succeed on development/production, so this is a "workaround".</p>
  
  <pre><code>if current_app.config["TESTING"] != True:
      def migrate(migrator, database, fake=False, **kwargs):
          """Write your migrations here."""
  
          migrator.sql('alter table {} drop constraint {};'.format(TABLE_NAME, CONSTRAINT_NAME))
  
          migrator.sql('alter table {} add constraint {} foreign key (device_id) references device(device_id) on delete cascade;'.format(TABLE_NAME, CONSTRAINT_NAME))
  
  
      def rollback(migrator, database, fake=False, **kwargs):
          """Write your rollback migrations here."""
  
          migrator.sql('alter table {} drop constraint {};'.format(TABLE_NAME, CONSTRAINT_NAME))
  
          migrator.sql('alter table {} add constraint {} foreign key (device_id) references device(device_id);'.format(TABLE_NAME, CONSTRAINT_NAME))
  else:
      def migrate(migrator, database, fake=False, **kwargs):
          """Write your migrations here."""
  
      def rollback(migrator, database, fake=False, **kwargs):
          """Write your rollback migrations here."""
  </code></pre>
  
  <p>As sqlite doesn't support <code>alter table drop constraint</code>, I understand that you need to create a copy of a table, insert all the data from the original table into a copy, and then drop the original table and rename the copy table, but I think that's an overkill. </p>
  
  <p>Can you suggest me a better way to get this done? Can I do it with peewee_migrate somehow without using custom sql, so that peewee can be database-agnostic?</p>