Каков типичный метод предотвращения дублирования в таблице PostgreSQL?
Рассмотрим следующую таблицу recipe в базе данных PostgreSQL.
Предположим, пользователь хочет добавить в таблицу новый рецепт. Пользователь не хочет дублирования в таблице.
Как лучше всего вставлять новую строку в эту таблицу без дубликатов?
Решение 1
Прежде чем добавлять какие-либо рецепты, введите схему идентификации. Можно сказать, что любые два рецепта с одинаковыми name и ingredients являются дубликатами. Цель состоит в том, чтобы предотвратить возникновение дубликатов, поэтому генерируется уникальный идентификатор на основе названия рецепта и ингредиентов (каким-то образом), а столбец добавляется в базу данных.
Затем мы можем использовать этот идентификатор, имеющий уникальный первичный ключ в таблице.
Один (плохой) возможный способ создания этого уникального идентификатора — объединить name и ingredients любого нового рецепта, хэшировать его и получить из него уникальную 256-битную строку.
Если я найду рецепт в Интернете, вставлю «новый» рецепт в свою таблицу и обнаружу, что идентификатор уже существует, я знаю, что на самом деле это не новый рецепт, и могу безопасно прервать вставку.
Решение 2
Создайте составной первичный ключ, состоящий из столбцов name и ingredients.
Следующий пример довольно плох, но потерпите меня.
Если я запущу поиск рецептов в Интернете и добавлю рецепты в свою таблицу, старые рецепты, которые были обновлены для большей точности calories, будут обновлены.
Решение 3
При каждой добавлении в таблицу сверяйте новый рецепт с name и ingredients всех существующих рецептов. Если есть коллизия, обновите столбец calories рецепта в таблице.
Если мой пример recipe масштабируется до тысяч или миллионов строк, решения 1 и 3 не работают.
Риски и ограничения
Решение 1 не работает, потому что коллизии хеширования становятся реальной возможностью в больших масштабах.
Решение 3 «ломается», сравнивая каждую новую запись с каждой строкой в очень большой таблице было бы ужасно неэффективно.
Просто создайте уникальный указатель по названию и ингредиентам.
{"stuff", "pasta"} тот же рецепт, что и '{"pasta", "stuff"}'?
@AdrianKlaver спасибо за ответ. Хотя вы абсолютно правы, этот пример в лучшем случае груб. Суть вопроса заключается в однозначной идентификации строк данных без произвольного столбца идентификатора.
@yurikilochek это было бы решение №2. Спасибо за ваш совет.
@Schwern предполагает, что список ингредиентов отсортирован в алфавитном порядке и все строчные буквы без опечаток. Т.е. два списка ингредиентов с одинаковыми ингредиентами всегда будут идентичными.
Ответы 3
Какие поля составляют «уникальную идентификацию» — это бизнес-требование, а не техническое. Например, разумным и общепринятым было бы указать, что название рецепта должно быть уникальным, и все, не нужно усложнять больше, и нет смысла говорить, что два рецепта различны только потому, что я написал 815 калорий, а вы написали 810, остальное то же самое. Затем, чтобы создать вариант/немного другую версию уже существующего рецепта, просто дайте ему новое, уникальное имя, которого будет достаточно, чтобы различать, что есть что, когда пользователь просматривает список названий рецептов в каталоге... Или, например. для объекта «Клиенты» в системе бухгалтерского учета, возможно, потребуется НДС, если он уникален на национальном уровне (они есть не во всех странах, и необходим некоторый дополнительный элемент); в то время как для сайта электронной коммерции обычно достаточно электронной почты, поскольку этого достаточно для индивидуальной юридической ответственности в случае возникновения дела, в то время как человек с несколькими учетными записями (под разными адресами электронной почты) сам по себе не является проблемой. И так далее, это всего лишь примеры рассуждений.
OTOH, техническая проблема относительно того, как реализовать любые такие ограничения уникальности, на самом деле заключается в следующем: либо создать первичный ключ из этих полей, либо (что более распространено) поместить идентификатор или GUID в таблицу для ПК, но также наложить ограничение уникальности на поле или группу полей, которые должны быть уникальными.
Наконец, что касается производительности: при правильном использовании СУБД таких проблем не возникает, ведь СУБД якобы реализует наиболее оптимизированный функционал в этом смысле. В частности, ограничение уникальности основано на базовом уникальном индексе (аналогично для ПК), и для обеспечения соблюдения этих ограничений не требуется полное сканирование строк таблицы (возможно, с некоторыми оговорками, которые зависят от конкретной СУБД). .
Хулио, спасибо за ответ. Вы развеяли мое замешательство.
Предполагая, что вам не нужно сохранять уникальное имя рецепта, а скорее определить, совпадают ли имя рецепта и ингредиенты, у вас есть два варианта.
- Если список ингредиентов не слишком длинный
Создайте таблицу рецептов и индекс для таблиц названий и ингредиентов.
Вы можете выполнить поиск в индексированной таблице, если результаты существуют, а затем добавить те, которых нет. В последней версии Postgres есть функция объединения индексов, чтобы ускорить выполнение запросов.
- Если у вас список ингредиентов больше
Создайте таблицу рецептов, таблицу ингредиентов и таблицу рецепта_ингредиентов для отношений «многие ко многим».
Таблица рецепта_ингредиентов может быть создана с использованием внешних ключей или обычных индексов, на ваше усмотрение.
Если вы правильно используете индексы, поиск в базе данных перед вставкой новой строки не будет проблемой.
Если вы просто хотите сохранить уникальность имени и не заботитесь об ингредиентах, просто сделайте поле имени уникальным, и этого будет достаточно.
Самое приятное в вашем ответе — это создание структуры 1-Many. ПК для одной стороны будет названием рецепта, а ПК для ингредиентов будет названием ингредиента. Для дочернего элемента можно создать уникальный индекс, чтобы комбинация IngridiantName+RecipeName была уникальной. Обратите внимание, что это не имеет никакого отношения к тому, маленькая или большая таблица.
Создайте уникальный указатель названий и ингредиентов.
create unique index recipe_uniq_idx on recipe(name, ingredients);
Это работает только в том случае, если ингредиенты одинаковы и расположены в том же порядке.
Это также действует как индекс по имени. ингредиенты должны иметь свой индекс джина.
В качестве первичного ключа используйте обычный целочисленный первичный ключ. Почему? Подумайте, использовали ли вы (name, ingredients) в качестве первичного ключа. Все, что хочет ссылаться на строку, должно копировать имя и все ингредиенты; это неудобно и медленно. То же самое, если вы хотите сослаться на рецепт в URL-адресе. Простое целое число делает ссылки простыми и маленькими.
Однако, хотя массивы и заманчивы, обычно лучше хранить списки в виде объединяющей таблицы. Большинство баз данных не поддерживают массивы, так что это тупик. Они делают запросы нестандартными. К ним нельзя прикрепить больше данных, разве ингредиенты в рецепте не нужны порциями?
create table recipes (
id serial primary key,
name text not null,
calories int not null,
-- Don't name a column "date", that's a keyword and it doesn't explain
-- what the date is for. Use timestamp for the extra accuracy, you can
-- always convert it to a date.
created_at timestamp not null default now()
);
create table recipe_ingredients (
recipe_id integer not null references recipes,
name text not null,
unit text not null,
portion numeric(6,2) not null
);
select r.name, array_agg(ri.name)
from recipes r
join recipe_ingredients ri on r.id = ri.recipe_id
group by r.id
Это затрудняет обнаружение дубликатов. Вы можете проверить перед вставкой вот так:
select r.id, r.name, array_agg(ri.name) as ingredients
from recipes r
join recipe_ingredients ri on r.id = ri.recipe_id
group by r.id
having array_agg(ri.name) @> $1 and array_agg(ri.name) <@ $1
Демонстрация.
1) Не делайте этого
ingredients text[], разнесите ингредиенты в отдельную таблицу. 2) Определить дубликаты будет сложно, потому что все, что вам нужно, это опечатки или различия в написании (например, цвет/цвет, серый/серый), чтобы это не сработало.