django - 如何在 Django 模型中设置表约束 "deferrable initially deferred"？

Question

我正在尝试使用 postgresql 数据库为 django 中的表模型设置约束。

我可以用这句话通过 postgresql 来完成:

ALTER TABLE public.mytable ADD CONSTRAINT "myconstraint" UNIQUE(field1, field2) DEFERRABLE INITIALLY DEFERRED;

但我想通过 django 模型来完成。阅读django官方文档我没有发现任何相关内容。

我需要这样的东西:

class Meta:
  unique_together = (('field1', 'field2',), DEFERRABLE INITIALLY DEFERRED)

有没有可能做这样的事情？

Answer 1

我会通过一次迁移来完成。首先以编程方式获取唯一约束名称，然后删除并重新添加(因为更改它似乎只适用于 FK 约束，而不适用于唯一约束)。添加也撤消此操作的反向迁移。

from django.db import migrations, connection



def _make_deferrable(apps, schema_editor):
    """
    Change the unique constraint to be deferrable
    """
    # Get the db name of the constraint
    MyModel = apps.get_model('myapp', 'MyModel')
    CONSTRAINT_NAME = schema_editor._constraint_names(MYModel,
                                                                   ['col1', 'col2'],
                                                                   unique=True)[0]
    TABLE_NAME = MyModel._meta.db_table


    # Drop then re-add with deferrable as ALTER doesnt seem to work for unique constraints in psql
    with schema_editor.connection.create_cursor() as curs:
        curs.execute(
            f'ALTER TABLE {TABLE_NAME} DROP CONSTRAINT "{CONSTRAINT_NAME}";'
        )
        curs.execute(
            f'ALTER TABLE {TABLE_NAME} ADD CONSTRAINT'
            f' {CONSTRAINT_NAME}'
            f' UNIQUE (col1, col2) DEFERRABLE INITIALLY DEFERRED;'
        )


def _unmake_deferrable(apps, schema_editor):
    """
    Reverse the unique constraint to be not deferrable
    """
    # Get the db name of unique constraint
    MyModel = apps.get_model('myapp', 'MyModel')
    CONSTRAINT_NAME = schema_editor._constraint_names(MyModel,
                                                                   ['col1', 'col2'],
                                                                   unique=True)[0]
    TABLE_NAME = MyModel._meta.db_table

    with schema_editor.connection.create_cursor() as curs:
        curs.execute(
            f'ALTER TABLE {TABLE_NAME} DROP CONSTRAINT "{CONSTRAINT_NAME}";'
        ) 
        curs.execute(
            f'ALTER TABLE {TABLE_NAME} ADD CONSTRAINT'
            f' {CONSTRAINT_NAME}'
            f' UNIQUE (col1, col2) NOT DEFERRABLE;'
        )

class Migration(migrations.Migration):

    dependencies = [
        ('myapp', '<previous_mig>'),
    ]

    operations = [
        migrations.RunPython(code=_make_deferrable,  reverse_code=_unmake_deferrable)
    ]