我有两个表(订单和设备),它们应该通过订单号链接在一起。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”的值。在“设备”表中会自动读取,但这并没有发生。那么该怎么做呢?
另外,当我在“设备”表中输入一行数据时,我会忽略其他外键,因为它们将通过它们的表链接吗?