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，则不必在其上创建唯一索引。