Избегайте повторяющихся «пар» значений (два столбца) в таблице SQL
У меня есть таблица, документирующая отношения между членами домохозяйства, и я хотел бы убедиться, что в ней всегда будет только одна запись с одними и теми же двумя людьми, но теперь есть два столбца и два значения для рассмотрения. Я провел пару часов в поисках, и, хотя я уверен, что он есть, я его не нашел.
У меня есть таблица для людей, чей внешний ключ (Persons.ID) здесь будет отображаться в aID и bID (два человека). У меня есть другая таблица для ролей (мать, дочь, дядя, брат и сестра и т. д.), чей внешний ключ (Roles.ID) будет отображаться в aID_role и bID_role). (Я пытаюсь хранить в этой таблице только целые числа, потому что в конечном итоге в ней может быть довольно много записей.)
Relationships
----------------------------------------
aID aID_role bID bID_role
----------------------------------------
4 1 5 2
Скажем, aID 4 — это Джон, а bID 5 — это Карла. aID_role 1 — «отец», а bID_role 2 — «дочь».
Как предотвратить ввод второй записи ниже?
Relationships
----------------------------------------
aID aID_role bID bID_role
----------------------------------------
4 1 5 2
5 2 4 1 <-- sort of a duplicate
Обратите внимание, что мне не интересно сравнивать столбцы «_role». Здесь они нематериальны; Я хочу, чтобы идентификаторы Person.ID 4 и 5 не появлялись более чем в одной записи в таблице «Отношения».
База данных находится в Azure SQL.
Спасибо Салман: Я предполагаю, что это то, что я должен сделать, но я боролся с тем, как это сделать. Я попытался что-то вроде "ALTER TABLE Relationship ADD CONSTRAINT ChkPair...", но я не мог понять, как заставить ограничение проверять значения два по столбцам два.
Вы уверены, что правильно моделируете роли? Не является ли роль свойством отношений в целом, а не той или иной стороны? т.е. Если b — это daughter из a, почему вы ожидаете что-то разное, чем father в другой позиции?
Спасибо, Дэмиен. Нет, я совсем не уверен, что это лучший способ смоделировать роли. Но нам нужно было иметь возможность сказать не только, что такое отношения, но и кто есть кто в отношениях. (Если это просто «родитель», то кто является родителем?) Таким образом, казалось имеет смысл хранить роль для каждого человека в отношениях. Я тоже порылся в сети по этому вопросу и ничего не нашел. Если есть лучший способ моделировать отношения, я, безусловно, заинтересован в этом.
показать, как вы вводите записи в таблицу отношений (SQL)
Майк, всегда ли это связь 1:1, или вы также хотите хранить что-то вроде компания друзей? Знает ли ваша таблица лиц пол человека? В этом случае вы можете довольно легко преобразовать родитель в отец или мама. Одно быстрое предложение: определите еще одну таблицу с направленные отношения, например (родитель или даже отец дочери), и сократите свою таблицу до трех столбцов, где вы принудительно установите идентификатор человека в a и b в направлении отношений...
Томас, я просто использую простой оператор вставки: insert into Relationships (aID, aID_role, bID, bID_role) VALUES (4,1,5,4);. Я собираюсь дать ответ Дэмиена ниже; это похоже на победителя. Спасибо!
Спасибо Шнуго. Это всегда отношение 1:1. Если я использую направленные отношения, у меня либо будет в два раза больше записей в таблице, либо мне потребуется дополнительный код, который экстраполирует обратную связь (возможно, это неплохо).
Майк, одним из возможных улучшений может быть (если вам это нужно) создание таблицы отношений со столбцом IsOppositeOf, допускающим значение NULL. Это позволит пользователю выбрать отец или ребенок из выбора. При сохранении этих данных вы должны использовать только ведущие отношения (с NULL в этом столбце)...
Ответы 3
Если вы хотите предотвратить вставку «противоположной» строки, вам необходимо реализовать уникальное ограничение для пары столбцов, которым гарантируется определенное свойство (например, первый всегда ниже второго).
Для неориентированных графов простым подходом является проверка ограничения на таблицу, а затем обеспечение вставки данных в соответствии с ограничением.
Однако отношения, как правило, представляют собой отношения направленный. Например. У меня была бы таблица с FromID, ToID и Relationship, где последний будет искать такие значения, как «от отца к дочери» (которые вы можете прочитать с неявным «от» в начале), что ясно дает понять, что такое FromID и это ToID.
Такая структура ясно дает понять, что есть только один правильный способ сохранения этих отношений, но вы все равно не сможете гарантировать, что, например. FromID всегда ниже, чем ToID, и может захотеть предотвратить случайные вставки, которые являются неправильными (не допустить, чтобы кто-то одновременно был матерью и дочерью кого-то другого)
Итак, мы вводим индексированное представление, который синтезирует пару столбцов с этим отношением, а затем накладывает на них уникальное ограничение.
CREATE VIEW dbo.Relationships_DRI_Unique
WITH SCHEMABINDING
AS
SELECT
CASE WHEN FromID < ToID THEN FromID ELSE ToID END as Col1,
CASE WHEN FromID < ToID THEN ToID ELSE FromID END as Col2
FROM
dbo.Relationships
GO
CREATE UNIQUE INDEX IX_Relationships_DRI_Unique on dbo.Relationships_DRI_Unique (Col1,Col2)
(DRI = декларативная ссылочная целостность. Это мое собственное соглашение использовать эти буквы в именовании объектов, которые существуют исключительно для обеспечения соблюдения ограничения. Я не ожидаю, что кто-либо когда-либо будет запрос это представление)
Вы можете создать вычисляемые столбцы, которые отражают идентификаторы, например:
id1 AS (CASE WHEN aID < bID THEN aid ELSE bid END),
id2 AS (CASE WHEN aID < bID THEN bid ELSE aid END),
Тогда две строки дубликат будут выглядеть так:
aID bID id1 id2
4 5 4 5
5 4 4 5
И вам просто нужно создать ограничение UNIQUE:
CONSTRAINT IX_Relationships UNIQUE (id1, id2)
Это выглядит великолепно! Как я могу сравнить этот метод с методом, предложенным Дэмиеном в своем ответе, чтобы увидеть, какой из них менее затратен для сервера?
Вы можете настроить любое решение для повышения производительности (я полагаю, что вычисляемые столбцы могут быть проиндексированы).
@МайкНовиски . . . Эта версия проще. Решение Дэмиена требует создания отдельной таблицы в базе данных для ограничения уникальности. Это решение просто включает в себя изменения в одной таблице, для которой требуется ограничение.
предложение новой модели:
table persons
id PK
first_name,
second_name
table roles
id PK
role_id
role_name (ex. Father, daugther,)
table relationship
id PK
person (ex. persons.id = 1, JOHN)
role (roles.id = 5, FATHER
person_to (persons.id = 3, CARLA,
короче говоря, «человек» — это «роль» для «человека_кому».
Пример из вашего вопроса: вам нужно добавить только 1 запись в отношение таблицы для:
«ДЖОН» — это «ОТЦ» «КАРЛЫ».
нет необходимости добавлять второй НРАВИТСЯ
"КАРЛА" - "дочь" "ДЖОНА"
Спасибо! Хотя это выглядит проще, как обеспечить целостность данных? Что помешало бы кому-то позже ввести запись «Карла - дочь Джона» (ненужная дополнительная запись, как вы указываете) или, что еще хуже, «Карла - двоюродная сестра Джона» (они сделали неправильный выбор). Теперь у нас есть две записи, которые конфликтуют друг с другом. Вот почему мне нужно предотвратить «повторяющиеся пары»: идентификатор Карлы и идентификатор Джона должны появляться вместе в записи только один раз во всей таблице — независимо от того, использую ли я направленную связь или сохраняю обе роли.
Второй сценарий - подумайте об ограничении UNIQUE для столбцов "person" и "person_to". В этом случае - если Карла - дочь Джона, запись существует, то запись Карла - двоюродная сестра Джона вызовет ошибку. Первый сценарий. Это можно сделать с помощью триггера «ДО ЗАЯВЛЕНИЯ», чтобы проверить, существуют ли какие-либо отношения между этими двумя людьми. Но будьте осторожны - если эта таблица станет большой, и перед любой вставкой/обновлением потребуется полное сканирование, это может вызвать проблемы с производительностью. Подумайте о некоторых индексах.
Вы хотите проверить ограничение (и т. д.) или запрос для проверки возможных дубликатов?