django - Postresql 唯一约束不是触发器

问题描述

我正在将 Django 与 Postresql 一起使用。我正在尝试创建一个具有 6 个约束的模型，其中 5 个正在工作，但UNIQUE约束不知何故被触发了。我查看了生成的数据库，但在我看来，它看起来像是有意的……有人发现我的错误？

这是生成的表：

-- auto-generated definition
create table permissionentry
(
    id                serial    not null constraint permissionentry_pkey  primary key,
    permission_type   smallint  not null,
    source_group_id   integer
        constraint permissionentr_source_group_id_19769394_fk_usc
            references group
            deferrable initially deferred,
    source_profile_id integer
        constraint permissionentr_source_profile_id_54191816_fk_usc
            references profile
            deferrable initially deferred,
    target_group_id   integer
        constraint permissionentr_target_group_id_bb111301_fk_usc
            references group
            deferrable initially deferred,
    target_profile_id integer
        constraint permissionentr_target_profile_id_e97cadb4_fk_usc
            references profile
            deferrable initially deferred,

    constraint "constrainError.permissionExists"
        unique (source_profile_id, source_group_id, target_profile_id, target_group_id),

    constraint "constrainError.onlyOneSource"
        check (((source_group_id IS NOT NULL) AND (source_profile_id IS NULL)) OR
               ((source_group_id IS NULL) AND (source_profile_id IS NOT NULL))),
    constraint "constrainError.onlyOneTarget"
        check (((target_group_id IS NOT NULL) AND (target_profile_id IS NULL)) OR
               ((target_group_id IS NULL) AND (target_profile_id IS NOT NULL))),
    constraint "constrainError.groupsCantBeSupervisor"
        check ((source_group_id IS NULL) OR ((permission_type > 1) AND (source_group_id IS NOT NULL)))
);

alter table permissionentry
    owner to alamanda;

create index permissionentry_source_group_id_19769394
    on permissionentry (source_group_id);

create index permissionentry_source_profile_id_54191816
    on permissionentry (source_profile_id);

create index permissionentry_target_group_id_bb111301
    on permissionentry (target_group_id);

create index permissionentry_target_profile_id_e97cadb4
    on permissionentry (target_profile_id);

create unique index "constrainError.onlyOneSupervisorPerGroup"
    on permissionentry (target_group_id)
    where (permission_type = 1);

create unique index "constrainError.onlyOneSupervisorPerProfile"
    on permissionentry (target_profile_id)
    where (permission_type = 1);

这是生成此表的模型

class PermissionEntry(models.Model):
    source_profile = models.ForeignKey(
        'Profile',
        on_delete=models.CASCADE,
        blank=True,
        null=True,
        related_name='entry_source_profile'
    )
    source_group = models.ForeignKey(
        'Group',
        on_delete=models.CASCADE,
        blank=True,
        null=True,
        related_name='entry_source_group'
    )

    target_profile = models.ForeignKey(
        'Profile',
        on_delete=models.CASCADE,
        blank=True,
        null=True,
        related_name='entry_target_profile'
    )

    target_group = models.ForeignKey(
        'Group',
        on_delete=models.CASCADE,
        blank=True,
        null=True,
        related_name='entry_target_group'
    )

    permission_type = models.SmallIntegerField(
        choices=PERMISSION_TYPES,
        blank=False,
    )

    class Meta:
        constraints = [
            models.CheckConstraint(
                check=Q(source_profile__isnull=True, source_group__isnull=False) | Q(source_group__isnull=True,
                                                                                     source_profile__isnull=False),
                name='constrainError.onlyOneSource'
            ),
            models.CheckConstraint(
                check=Q(target_profile__isnull=True, target_group__isnull=False) | Q(target_group__isnull=True,
                                                                                     target_profile__isnull=False),
                name='constrainError.onlyOneTarget'
            ),
            models.UniqueConstraint(
                fields=['source_profile', 'source_group', 'target_profile', 'target_group'],
                name="constrainError.permissionExists"
            ),
            models.UniqueConstraint(
                fields=['target_group'],
                condition=Q(permission_type=1),
                name='constrainError.onlyOneSupervisorPerGroup'
            ),
            models.UniqueConstraint(
                fields=['target_profile'],
                condition=Q(permission_type=1),
                name='constrainError.onlyOneSupervisorPerProfile'
            ),
            models.CheckConstraint(
                check=Q(source_group__isnull=True) | Q(source_group__isnull=False, permission_type__gt=1),
                name='constrainError.groupsCantBeSupervisor'
            ),
        ]

对我来说，这张桌子看起来不错，但不知怎的，它就像意图一样工作。Databes 允许我在同一行插入两次......这不应该工作，还是我错了？

标签： djangopython-3.xpostgresqldjango-models

好吧，当其中有 Null 值时，Postgresql 的唯一约束显然会失败。所以我的解决方案是编写一些自定义 SQL 来创建具有所需行为的约束。

这是约束的 SQL

create unique index "constrainError.permissionExists" 
    on permission (
        coalesce(source_group_id, -1), 
        coalesce(source_profile_id, -1), 
        coalesce(target_group_id, -1), 
        coalesce(target_profile_id, -1)
    )

我像这样在迁移中执行它：

migrations.RunSQL(
    'create unique index "constrainError.permissionExists" on permission (coalesce(source_group_id, -1), coalesce(source_profile_id, -1), coalesce(target_group_id, -1), coalesce(target_profile_id, -1))',
    'drop index "constrainError.permissionExists"'
)

如果值是，coalesce()则函数插入 -1 null，因此约束中断。

django - Postresql 唯一约束不是触发器

问题描述

解决方案

推荐阅读