title: MySQL Composite PK with Nullable FKs
body: <p>First off, let me preface this question by stating that I'm really a pretty terrible data modeler.  I know only enough to be dangerous.</p>
  
  <p>The table I'm building has four foreign keys, two of which reference the same table.  Here's the create statement for that table.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The indexes I'm concerned with for this post are <code>fk_reputation_log_user</code> and <code>fk_reputation_log_user1</code>.  Every reputation event has a giver, but only some have a receiver.  I would <em>like</em> this FK to be nullable, but I don't know how to do that, or if it's even "allowed".</p>
  
  <p>I also toyed with the idea of making all FK columns part of the primary key to have database-level protection against duplicate log entries - but that won't work since PK columns have to be NOT NULL.</p>
  
  <p>If you need more detail, please state that in the comments.  Thanks!</p>
  
  <p>(Yes, this is for a reputation system not too dissimilar from what SO has)</p>