MySQL:如何在有两个外键的表中插入值

Question

我将MySQL与NodeJS结合使用，我的表如下所示：

CREATE TABLE IF NOT EXISTS `keycomponent`.`Transaction` (
  `TransactionID` INT NOT NULL,
  `TransactionDate` DATE NOT NULL,
  `Amount` MEDIUMTEXT NOT NULL,
  `CreationDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `TransactionDescriptionPK` INT NOT NULL,
  `FilePK` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`TransactionID`),
  UNIQUE INDEX `TransactionID_UNIQUE` (`TransactionID` ASC),
  INDEX `FilePK_idx` (`FilePK` ASC),
  INDEX `TransactionDescriptionPK_idx` (`TransactionDescriptionPK` ASC),
  CONSTRAINT `TransactionDescriptionPK`
    FOREIGN KEY (`TransactionDescriptionPK`)
    REFERENCES `keycomponent`.`TransactionDescription` (`TransactionDescriptionPK`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FilePK`
    FOREIGN KEY (`FilePK`)
    REFERENCES `keycomponent`.`File` (`Filename`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

它包含两个外键，当我试图将值插入到另一个表中时，会得到以下错误

Error: ER_NO_DEFAULT_FOR_FIELD: Field 'TransactionDescriptionPK' doesn't have a default value

列不应该自动从另一个表中获得主键吗？

我的疑问

    'INSERT INTO Transaction '+
              'SET TransactionID = ?, TransactionDate = ?, Amount = ?, TransactionDescriptionPK = ?, FilePK = ?',
[transId,date,amount,"primary key to the TransactionDescription table","primary key to the File table"]

Answer 1

不，外键不会自动从另一个表中获得值。mysql应该如何确定它应该从父表中获得哪个TransactionDescriptionPK值？您必须向声明为null且没有默认值的所有外键字段提供一个值，就像对任何其他类似字段所做的那样。

附带注意:如果字段声明为pk，则不必在其之上创建唯一的索引。

Answer 2

我不相信你的错误与外键有任何关系。在您的TransactionDescriptionPK表中有一个列TransactionDescription，它被设置为not null，但是在这个insert语句中没有为它提供任何值。