Використання LEAST та GREATEST для композитних ключів в SQL

Ефективне управління дубльованими записами часто вимагає використання композитних ключів. Функції SQL, такі як LEAST та GREATEST, допомагають створювати нормалізовані композитні ключі, спрощуючи виявлення та вирішення конфліктів.

Завдання

У системах, які відстежують дублікати, зв'язки між записами (наприклад, основний запис і дублікати) повинні зберігатися унікально. Наприклад:

CREATE TABLE public.duplicate_records (  
 id bigserial NOT NULL,  
 duplicate_id int8 NOT NULL,  
 record_type varchar NOT NULL,  
 record_id int8 NOT NULL,  
 duplicate_level_id int8 NOT NULL,  
 created_at timestamp(6) NOT NULL,  
 updated_at timestamp(6) NOT NULL,  
 CONSTRAINT duplicate_records_pkey PRIMARY KEY (id),  
);  
CREATE UNIQUE INDEX duplicate_records_unique_index ON public.duplicate_records USING btree (LEAST(duplicate_id, record_id), GREATEST(duplicate_id, record_id), record_type);

Використання функцій LEAST та GREATEST забезпечує послідовне впорядкування duplicate_id та record_id, розглядаючи зв'язки, такі як (A, B) та (B, A), як ідентичні. Ця нормалізація є критично важливою для запобігання надлишковим записам і забезпечення цілісності даних.

Запит

Наступний запит демонструє, як ефективно вставляти або оновлювати дублікати записів:

INSERT INTO duplicate_records (duplicate_id, record_type, record_id, duplicate_level_id, created_at, updated_at) (values)  
ON CONFLICT (LEAST(duplicate_id, record_id), GREATEST(duplicate_id, record_id), record_type)  
DO UPDATE  
SET  
 duplicate_level_id = EXCLUDED.duplicate_level_id  
 updated_at = EXCLUDED.updated_at

Як це працює

• Функції LEAST та GREATEST нормалізують порядок duplicate_id та record_id, забезпечуючи послідовне збереження зв'язків дубліката.
• Композитний ключ (LEAST, GREATEST, record_type) дозволяє ефективно виявляти конфлікти.

Практичні застосування

Цей підхід ідеально підходить для:

• Систем виявлення дублікатів: Обробка дублікатів у системах управління клієнтами або каталогами.
• Двозначні зв'язки: Керування будь-яким сценарієм, де зв'язки не залежить від порядку.

Перекладено з: Using LEAST and GREATEST for Composite Keys in SQL