處理數據輸入中的外鍵

我有兩個表（訂單和設備），它們應該通過訂單號連結在一起。SQL腳本如下：

-- -----------------------------------------------------
-- Table `rr`.`order`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `rr`.`order` (
 `Order_No.` INT UNSIGNED NOT NULL,
 `Order_Type` CHAR(8) NOT NULL,
 `Equipment_Quantity` INT UNSIGNED NOT NULL,
 PRIMARY KEY (`Order_No.`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = '\\n';

-- -----------------------------------------------------
-- Table `rr`.`equipment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `rr`.`equipment` (
 `S_N` INT UNSIGNED NOT NULL,
 `OrderNo.` INT UNSIGNED NOT NULL,
  `V_N` INT UNSIGNED NOT NULL,
 `LS_N` INT UNSIGNED NOT NULL,
 `FK_EQ_Inspection_ID` INT UNSIGNED NOT NULL,
 `FK_EQ_Reinspect_ID` INT UNSIGNED NOT NULL,
 `FK_EQ_Scentencing_Notification_No` INT UNSIGNED NOT NULL,
 `FK_EQ_Repair_ID` INT UNSIGNED NOT NULL,
 PRIMARY KEY (`S_N`, `OrderNo.`, `FK_EQ_Scentencing_Notification_No`),
 INDEX `OrderNo._idx` (`OrderNo.` ASC) VISIBLE,
 INDEX `FK_EQ_Notification_No_idx` (`FK_EQ_Inspection_ID` ASC) VISIBLE,
 INDEX `FK_EQ_Confirmation No._idx` (`FK_EQ_Reinspect_ID` ASC) VISIBLE,
 INDEX `fk_equipment_scentencing1_idx` (`FK_EQ_Scentencing_Notification_No` ASC) VISIBLE,
 INDEX `FK_EQ_Repair_ID_idx` (`FK_EQ_Repair_ID` ASC) VISIBLE,
 CONSTRAINT `FK_EQ_Reinspect_ID`
   FOREIGN KEY (`FK_EQ_Reinspect_ID`)
   REFERENCES `rr`.`re-inspect` (`Reinspect_ID`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT `Inspection_ID`
   FOREIGN KEY (`FK_EQ_Inspection_ID`)
   REFERENCES `rr`.`inspection` (`Inspection_ID`),
 CONSTRAINT `OrderNo.`
   FOREIGN KEY (`OrderNo.`)
   REFERENCES `rr`.`order` (`Order_No.`),
 CONSTRAINT `FK_EQ_Scentencing_Notification_No`
   FOREIGN KEY (`FK_EQ_Scentencing_Notification_No`)
   REFERENCES `rr`.`scentencing` (`Scentencing_Notification_No`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION,
 CONSTRAINT `FK_EQ_Repair_ID`
   FOREIGN KEY (`FK_EQ_Repair_ID`)
   REFERENCES `rr`.`repair` (`Repair_ID`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

USE `rr`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `rr`.`before_equipments_insert`
BEFORE INSERT ON `rr`.`equipment`
FOR EACH ROW
BEGIN
IF NEW.V_N = 1 THEN
    SET new.LS_N = 0;
END IF;

END$$

USE `rr`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `rr`.`before_equipments_UPDATE`
BEFORE UPDATE ON `rr`.`equipment`
FOR EACH ROW
BEGIN
IF NEW.V_N = 1 THEN
    SET new.LS_N = 0;
END IF;

END$$

通過檢查“設備”表，它包括“訂單號”。作為來自“訂單”表的外鍵以及來自其他不同表的其他外鍵。

我為“ORDER”表輸入了一行數據，我期待的是外鍵“Order_No”的值。在“設備”表中會自動讀取，但這並沒有發生。那麼該怎麼做呢？

另外，當我在“設備”表中輸入一行數據時，我會忽略其他外鍵，因為它們將通過它們的錶鍊接嗎？

除非您在外鍵聲明中使用CASCADE說明符，否則外鍵不會在子引用表中自動生成。請參閱為什麼外鍵不更新？有關如何將 CASCADE 與外鍵和替代解決方案一起使用的更多資訊。

引用自：https://dba.stackexchange.com/questions/281631