Added fk_name parameter to ormar.ForeignKey by nikelborm · Pull Request #849

Added fk_name parameter to ormar.ForeignKey by nikelborm · Pull Request #849 · ormar-orm/ormar

It will allow users to override automatically generated foreign key constraint name (for example in case when generated constraint name is too long for some DB)

In my case this code

...
class UserChosenConfiguration(ormar.Model):
  class Meta(BaseMeta):
    tablename = "user_chosen_configuration"
    constraints = [
      ormar.UniqueColumns("widget_instance_id", "widget_configuration_parameter_id", name="uc_user_chosen_configuration_pair")
    ]

  id: int = ormar.Integer(name="user_chosen_configuration_id", nullable=False, primary_key=True, autoincrement=True)

  widgetInstance: Optional[WidgetInstance]                             = ormar.ForeignKey(WidgetInstance,               name="widget_instance_id",                nullable=False, unique=False)
  widgetConfigurationParameter: Optional[WidgetConfigurationParameter] = ormar.ForeignKey(WidgetConfigurationParameter, name="widget_configuration_parameter_id", nullable=False, unique=False)
  value: str                                                           = ormar.Text(                                    name="value",                             nullable=False)
...

when used to generate migration causes such alembic migration:

...
def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    ...
    op.create_table('user_chosen_configuration',
    sa.Column('user_chosen_configuration_id', sa.Integer(), nullable=False),
    sa.Column('widget_instance_id', sa.Integer(), nullable=False),
    sa.Column('widget_configuration_parameter_id', sa.CHAR(32), nullable=False),
    sa.Column('value', sa.Text(), nullable=False),
    sa.ForeignKeyConstraint(['widget_configuration_parameter_id'], ['widget_configuration_parameter.widget_configuration_parameter_id'], name='fk_user_chosen_configuration_widget_configuration_parameter_widget_configuration_parameter_id_widgetConfigurationParameter'),
    sa.ForeignKeyConstraint(['widget_instance_id'], ['widget_instance.widget_instance_id'], name='fk_user_chosen_configuration_widget_instance_widget_instance_id_widgetInstance'),
    sa.PrimaryKeyConstraint('user_chosen_configuration_id'),
    sa.UniqueConstraint('widget_instance_id', 'widget_configuration_parameter_id', name='uc_user_chosen_configuration_pair')
    )
    # ### end Alembic commands ###
...

and when I try to execute it, migration fails with such error:

(base) nikel@nikel-A35S:~/tda-dashboards/database_experiment$ cd ~/tda-dashboards/database_experiment; DATABASE_HOST=$(docker inspect -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' mysql-experiment) DATABASE_PORT="3306" DATABASE_USERNAME="root" DATABASE_PASSWORD="password" DATABASE_NAME="test" alembic upgrade head
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 1a681990b866, initial experiments
Traceback (most recent call last):
  File "/home/nikel/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/home/nikel/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1059, "Identifier name 'fk_user_chosen_configuration_widget_configuration_parameter_widget_configuration_parameter_id_widget' is too long")

so I found a way to fix it, by adding fk_name parameter to ormar.ForeignKey
and it allows me to specify foreign constraint name like this:

...
class UserChosenConfiguration(ormar.Model):
  class Meta(BaseMeta):
    tablename = "user_chosen_configuration"
    constraints = [
      ormar.UniqueColumns("widget_instance_id", "widget_configuration_parameter_id", name="uc_user_chosen_configuration_pair")
    ]

  id: int = ormar.Integer(name="user_chosen_configuration_id", nullable=False, primary_key=True, autoincrement=True)

  widgetInstance: Optional[WidgetInstance]                             = ormar.ForeignKey(WidgetInstance,               name="widget_instance_id",                nullable=False, unique=False, fk_name="asd2")
  widgetConfigurationParameter: Optional[WidgetConfigurationParameter] = ormar.ForeignKey(WidgetConfigurationParameter, name="widget_configuration_parameter_id", nullable=False, unique=False, fk_name="asd1")
  value: str                                                           = ormar.Text(                                    name="value",                             nullable=False)
...

and it creates migration which executes successfully:

...
op.create_table('user_chosen_configuration',
    sa.Column('user_chosen_configuration_id', sa.Integer(), nullable=False),
    sa.Column('widget_instance_id', sa.Integer(), nullable=False),
    sa.Column('widget_configuration_parameter_id', sa.CHAR(32), nullable=False),
    sa.Column('value', sa.Text(), nullable=False),
    sa.ForeignKeyConstraint(['widget_configuration_parameter_id'], ['widget_configuration_parameter.widget_configuration_parameter_id'], name='asd1'),
    sa.ForeignKeyConstraint(['widget_instance_id'], ['widget_instance.widget_instance_id'], name='asd2'),
    sa.PrimaryKeyConstraint('user_chosen_configuration_id'),
    sa.UniqueConstraint('widget_instance_id', 'widget_configuration_parameter_id', name='uc_user_chosen_configuration_pair')
    )
...