Уникальный индекс, когда один из двух столбцов равен нулю

(Rails 4.2.1, Sqlite3) У меня есть три модели — M1, M2, M3.

M1 принадлежит M2

M1 также принадлежит M3

M1 имеет поле :name (строка).

У меня есть следующие ограничения, которые должны быть проверены:

1) С записью M1 может быть связана запись M2 или M3, но не обе одновременно.

2) Имя M1 должно быть уникальным в зависимости от того, какой из M2 или M3 указан.

Я реализовал ограничение (1) в модели, и оно работает так, как ожидалось. (Я упоминаю об этом только потому, что это может иметь отношение к сценарию).

Для ограничения (2) я добавил индекс в миграцию следующим образом:

add_index :m1s, [:name, :m2_id, :m3_id], unique: true, name: "idx_m1_name"

Тогда я звоню:

> m2 = M2.create! # success
> m1_1 = M1.create!(name: 'm1_1', m2: m2) #success
> m1_2 = M1.create!(name: 'm1_1', m2: m2) # this line should fail, but doesn't

Создаются m1_1 и m1_2 - я ожидаю, что m1_2 должен потерпеть неудачу из-за ограничения уникальности.

Я проверил, что индекс добавляется, как и ожидалось. Кроме того, согласно ограничению 1, m3_id равен нулю как в m1_1, так и в m1_2, не уверен, что это актуально.

Почему ограничение не проверяется?


ruby-on-rails database-indexes unique-constraint
person Anand    schedule 10.01.2016    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Итак, m3_id равно NULL в обоих случаях? В Sqlite3 нулевое значение считается отличным от другого нулевого значения в контексте уникального индекса.

Для целей уникальных индексов все значения NULL считаются отличными от всех других значений NULL и, таким образом, уникальны.

См. https://sqlite.org/lang_createindex.html.

Я думаю, что то же самое и в MySQL, и в Postgres.

person Thong Kuah    schedule 10.01.2016
comment
Спасибо, я подозревал нулевое значение, но ваш ответ и ссылка это подтверждают :-) ! Итак, как мне проверить указанное выше ограничение уникальности (2) в базе данных? - person Anand; 10.01.2016
comment
хм, так что вы можете иметь M2 или M3, но не оба. Рассматривали ли вы вместо этого использование полиморфных отношений? guides.rubyonrails.org/ - person Thong Kuah; 10.01.2016
comment
Заголовок stackoverflow.com/questions/12094479/ тоже возможно, но хакерский - person Thong Kuah; 10.01.2016
comment
Полиморфный не работает для моего сценария. У меня есть что-то вроде блога с вложенными сообщениями. Итак, M1 похож на пост, M2 — на блог, а M3 — на самом деле родительский пост. Таким образом, пост может быть корневым постом в блоге или дочерним постом другого поста, но не может быть и тем, и другим одновременно. - person Anand; 10.01.2016
comment
И да, я согласен, что другой хакки. Я мог бы создать проверку модели в рельсах, а не изменять нулевые значения по умолчанию. Я не могу сделать эти какие-то другие значения, потому что оба являются идентификаторами внешнего ключа, и, увидев их как ненулевые, вызовут всевозможные проблемы. Это должно быть довольно распространенной проблемой. Интересно, есть ли какой-нибудь элегантный способ решить эту проблему. - person Anand; 10.01.2016
comment
Я снял флажок с принятого ответа, чтобы другие могли ответить на него. Если ничего лучшего не найдется, я отмечу ваше как принятое. - person Anand; 10.01.2016
comment
Я принял твой ответ. Кроме того, вместо этого я просто использую проверки уровня модели. - person Anand; 11.01.2016

arrow_upward
0
arrow_downward

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

add_index :m1s, [:name, :m2_id], unique: true
add_index :m1s, [:name, :m3_id], unique: true

Поскольку нулевые значения считаются различными в целях обеспечения уникальности, первый индекс не будет ограничивать строки, в которых установлено только m3_id, и наоборот для второго индекса.

(Вас также может заинтересовать CHECK ограничение, которое проверяет, что ровно одно из m2_id или m3_id В Rails ограничения CHECK имеют небольшую оговорку: требуется, чтобы файл схемы выражено в SQL. Более общее решение для «индексов, которые применяются только к некоторым строкам» см. в разделе частичные индексы.)

person mpartel    schedule 15.01.2016
comment
спасибо за предложения. Подход с двумя индексами будет успешным, даже если и m2_id, и m3_id равны нулю. Требование, чтобы схема была в SQL, кажется слишком сложной для текущей проблемы, поэтому ограничение CHECK не работает. - person Anand; 16.01.2016
comment
Вы можете проверить, что оба значения не являются нулевыми, при проверке Rails. Такая проверка не подвержена состязаниям, в отличие от проверки уникальности. Я открыл вопрос об ограничениях CHECK. - person mpartel; 16.01.2016