ОГРАНИЧЕНИЕ MySQL для двух УНИКАЛЬНЫХ КЛЮЧЕЙ, которые используют одни и те же идентификаторы FOREIGN KEY ID

Я хочу создать таблицу MySQL для хранения данных об отношениях между пользователями. Отношения между пользователем A и B и пользователем B и A могут быть разными.

Пример:

  1. Отношение (от) Боба (к) Алисе: 0,9 - Боб любит вещи Алисы.
  2. Отношение (от) Алисы (к) Бобу: 0,5 — Алиса находит вещи Боба посредственными.

Мой вопрос:

Я реализовал два ОГРАНИЧЕНИЯ как УНИКАЛЬНЫЕ КЛЮЧИ для двух ВНЕШНИХ КЛЮЧЕЙ, которые ссылаются на user_id в таблице пользователей. Я могу это сделать? Они рассматриваются как два отдельных УНИКАЛЬНЫХ КЛЮЧА?

Как я могу реализовать ОГРАНИЧЕНИЕ, которое позволит только одно вхождение каждого отношения (от) UserA (к) UserB и (от) отношения UserB (к) UserA для каждого user_id? Правильно ли я поступаю?

SQL:

CREATE TABLE relationships (
  relationship_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  from_user_id MEDIUMINT UNSIGNED NOT NULL,
  to_user_id MEDIUMINT UNSIGNED NOT NULL,
  relationship_level DECIMAL(1,1) NOT NULL,
  PRIMARY KEY (relationship_id),

  FOREIGN KEY (from_user_id) REFERENCES users (user_id)
    ON DELETE CASCADE ON UPDATE NO ACTION,

  FOREIGN KEY (to_user_id) REFERENCES users (user_id)
    ON DELETE CASCADE ON UPDATE NO ACTION,

  CONSTRAINT from_to_relationship UNIQUE KEY (from_user_id, to_user_id),
  CONSTRAINT to_from_relationship UNIQUE KEY (to_user_id, from_user_id),

  INDEX relationship_from_to (relationship_id, from_user_id, to_user_id, relationship_level),
  INDEX relationship_to_from (relationship_id, to_user_id, from_user_id, relationship_level)

) ENGINE=INNODB;

Я надеюсь, что кто-то может помочь.


mysql constraints database-schema database-design unique-key
person leokennedy    schedule 12.02.2012    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Оставьте только одно из этих UNIQUE ограничений — нет необходимости иметь оба. Когда строка терпит неудачу UNIQUE KEY (from_user_id, to_user_id), она также терпит неудачу UNIQUE KEY (to_user_id, from_user_id) и наоборот, поэтому они логически эквивалентны. Даже с одним ограничением UNIQUE при попытке представить отношения между Алисой и Бобом у вас может быть не более одной строки {Алиса, Боб}, и не более одной строки {Боб, Алиса}.

Что касается производительности (т.е. обхода отношения в обоих направлениях), вы можете рассмотреть возможность индексации {from_user_id, to_user_id} (для «прямого» обхода) и/или {to_user_id, from_user_id} (для «обратного» обхода). Вы можете даже отказаться от суррогатного первичного ключа (relationship_id) и перейти на естественный PK, тем самым снизив потребность в индексации (вторичные индексы дороги для кластеризованных таблиц, см. Знакомство с кластерными индексами InnoDB, раздел "Недостатки кластеризации").

На мой взгляд, ваша таблица должна выглядеть так:

CREATE TABLE relationships (
    from_user_id MEDIUMINT UNSIGNED NOT NULL,
    to_user_id MEDIUMINT UNSIGNED NOT NULL,
    relationship_level DECIMAL(1,1) NOT NULL,
    PRIMARY KEY (from_user_id, to_user_id), -- InnoDB is clustered, so naturally "covers" relationship_level as well.
    FOREIGN KEY (from_user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION,
    FOREIGN KEY (to_user_id) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE NO ACTION,
    INDEX relationship_to_from (to_user_id, from_user_id, relationship_level) -- Including relationship_level may or may not be a good idea.
) ENGINE=INNODB;

ПРИМЕЧАНИЕ. Включаете ли вы relationship_level в ИНДЕКС или нет, зависит от того, хотите ли вы индексировать- сканировать только в обратном направлении. Направление «вперед» естественно покрывается PK (поскольку InnoDB сгруппированы).

person Branko Dimitrijevic    schedule 12.02.2012
comment
Большое спасибо за это, прояснил некоторые вещи. Однако в отношении PK (from_user_id, to_user_id) вы говорите, что InnoDB является кластеризованным, поэтому, естественно, также охватывает и уровень отношения. Означает ли это, что я могу получить доступ к этому конкретному уровню отношения непосредственно из этого PK из-за того, как таблица INNODB хранит строки на листовых страницах индекса? - person leokennedy; 12.02.2012
comment
@KennedyL Да. По сути, вся таблица хранится внутри индекса (кучи вообще нет), поэтому индекс естественно покрывает все поля таблицы, а не только те, которые явно указаны в PRIMARY KEY. Просто чтобы было ясно, покрытие вашего запроса индексом не имеет значения с логической точки зрения, но может иметь значение для производительности (пожалуйста, прочитайте ссылку Use The Index Luke о сканировании только индекса, которую я предоставил, чтобы понять, почему). - person Branko Dimitrijevic; 12.02.2012
comment
Отлично, большое спасибо, Бранко. Я внимательно прочитаю связанную статью. - person leokennedy; 12.02.2012