MySQL Composite PK с Nullable FKs

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

Таблица, которую я создаю, имеет четыре внешних ключа, два из которых ссылаются на одну и ту же таблицу. Вот инструкция создания для этой таблицы.

CREATE  TABLE IF NOT EXISTS `abnr`.`reputation_event_log` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `reputation_event_id` INT NULL ,
  `giver_user_id` INT NULL ,
  `receiver_user_id` INT NULL ,
  `review_id` INT NULL ,
  `giver_point_value` SMALLINT NULL DEFAULT 0 ,
  `receiver_point_value` SMALLINT NULL DEFAULT 0 ,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_reputation_log_user` (`giver_user_id` ASC) ,
  INDEX `fk_reputation_log_user1` (`receiver_user_id` ASC) ,
  INDEX `fk_reputation_log_review` (`review_id` ASC) ,
  INDEX `fk_reputation_log_reputation_event` (`reputation_event_id` ASC) ,
  CONSTRAINT `fk_reputation_log_user`
    FOREIGN KEY (`giver_user_id` )
    REFERENCES `abnr`.`user` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_reputation_log_user1`
    FOREIGN KEY (`receiver_user_id` )
    REFERENCES `abnr`.`user` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_reputation_log_review`
    FOREIGN KEY (`review_id` )
    REFERENCES `abnr`.`review` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_reputation_log_reputation_event`
    FOREIGN KEY (`reputation_event_id` )
    REFERENCES `abnr`.`reputation_event` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

В этом посте меня интересуют индексы fk_reputation_log_user и fk_reputation_log_user1. У каждого репутационного события есть даритель, но только у некоторых есть получатель. Я бы хотел, чтобы этот FK допускал значение NULL, но я не знаю, как это сделать, и если это вообще «разрешено».

Я также поигрался с идеей сделать все столбцы FK частью первичного ключа, чтобы иметь защиту на уровне базы данных от повторяющихся записей журнала, но это не сработает, поскольку столбцы PK должны быть НЕ NULL.

Если вам нужна более подробная информация, укажите это в комментариях. Спасибо!

(Да, это для системы репутации, не слишком отличной от той, что есть у SO)


mysql nullable foreign-keys data-modeling database-design
person Peter Bailey    schedule 02.12.2008    source источник

Ответы (1)


arrow_upward
4
arrow_downward 

CREATE  TABLE IF NOT EXISTS `abnr`.`reputation_event_log` (
  `id`                  INT NOT NULL AUTO_INCREMENT ,
  `reputation_event_id` INT NULL ,
  `giver_user_id`       INT NOT NULL , -- mandatory giver_user_id
  `receiver_user_id`    INT NULL ,     -- optional receiver_user_id
  . . .

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

Внешний ключ означает, что если столбец имеет значение, отличное от NULL, то это значение должно существовать в первичном ключе таблицы, на которую ссылается ограничение внешнего ключа.

edit: Что касается вашего UNIQUE требования, знаете ли вы, что вы можете объявить ограничение UNIQUE для столбцов, допускающих значение NULL. Столбец может содержать NULLs (в отличие от ограничения первичного ключа). Это стандартное поведение SQL, которое поддерживается MySQL.

  . . .
  PRIMARY KEY (`id`),
  CONSTRAINT UNIQUE (`giver_user_id`, `receiver_user_id`, 
                     `review_id`, `reputation_event_id`),
  . . .
person Bill Karwin    schedule 02.12.2008