Как я могу иметь отдельные условия триггера и фильтра в Django UniqueConstraint?

Учитывая следующие модели:

class Customer(models.Model):

    pass


class User(models.Model):

    email = models.EmailFIeld(blank=True, default="")

    customer = models.ForeignKey(Customer, ...)

Я хочу обеспечить выполнение следующего:

IF user has email
    IF user has customer
        email must be globally unique
    IF user has no customer
        email must be unique within the user's customer

Я попытался реализовать это с помощью двух UniqueConstraints:

UniqueConstraint(
    name="customer_scoped_unique_email",
    fields=["customer", "email"],
    condition=(
        Q(customer__isnull=False)
        & ~Q(email=None)
    ),
),
UniqueConstraint(
    name="unscoped_unique_email",
    fields=["email"],
    condition=(
        Q(customer=None)
        & ~Q(email=None)
    ),
),

Тестирование показало, что это все еще позволяет создать пользователя без клиента с email, идентичным существующему пользователю ( с клиентом). Насколько я понимаю, это происходит потому, что UniqueConstraint.condition определяет, когда должно срабатывать ограничение уникальности и какие другие записи включаются в проверку уникальности.

Есть ли способ достичь желаемой логики в базе данных , в идеале с поддержкой Django ORM, а в идеале с UniqueConstraint или CheckConstraint? Это должно происходить в базе данных. Это очевидно возможно в Python, но я хочу дополнительную надежность ограничения базы данных.

Есть ли способ достичь желаемой логики в базе данных ...

Да, вы можете использовать триггеры (см. SQL в разделе Триггеры ниже).

... в идеале в Django ORM-поддерживаемым способом ...

Не в рамках Django ORM, но для PostgreSQL, вы можете использовать django-pgtrigger для определения этого в моделях.

... и в идеале с UniqueConstraint или CheckConstraint?

Это не поддерживается на уровне базы данных, поскольку частичные индексы содержат только строки, основанные на WHERE.

Частичные индексы

UniqueConstraint.condition имеет те же ограничения базы данных, что и Index.condition.

  • PostgreSQL: https://www.postgresql.org/docs/8.0/indexes-partial.html

    .

    Частичный индекс - это индекс, построенный по подмножеству таблицы; подмножество определяется условным выражением (называемым предикатом частичного индекса). Индекс содержит записи только для тех строк таблицы, которые удовлетворяют предикату.

  • SQLite:

    https://www.sqlite.org/partialindex.html.

    Частичный индекс - это индекс по подмножеству строк таблицы.

    ... В частичных индексах только некоторое подмножество строк таблицы имеет соответствующие записи в индексе.

    В индекс включаются только те строки таблицы, для которых условие WHERE имеет значение true.

    • Триггеры

    Перед вставкой или обновлением в таблице user проверьте уникальный email.

    • Триггер PostgreSQL: 
    CREATE OR REPLACE FUNCTION unscoped_unique_email() RETURNS TRIGGER AS $unscoped_unique_email$
  DECLARE
    is_used_email bool;
  BEGIN
    IF NEW.email IS NOT NULL AND NEW.customer_id IS NULL THEN
        SELECT TRUE INTO is_used_email FROM user WHERE email = NEW.email;
        IF is_used_email IS NOT NULL THEN
            RAISE EXCEPTION 'duplicate key value violates unique constraint "unscoped_unique_email"'
            USING DETAIL = format('Key (email)=(%s) already exists.', NEW.email);
        END IF;
    END IF;
    RETURN NEW;
  END;
$unscoped_unique_email$ LANGUAGE plpgsql;

CREATE TRIGGER unscoped_unique_email BEFORE INSERT OR UPDATE ON user
  FOR EACH ROW EXECUTE PROCEDURE unscoped_unique_email();
Вернуться на верх