TransWikia.com

Handling the foreign keys in data entry

Database Administrators Asked by Ramy Khalifa on February 22, 2021

I have two tables (Order and equipment) and they should be linked together by the order No. the SQL script is as the following:

-- -----------------------------------------------------
-- 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$$

by checking the ‘equipment’ table, it includes ‘Order_No.’ as a foreign key from the ‘Order’ table in addition to other foreign keys from other different tables.

I entered the data of one row for the ‘ORDER’ table, I was expecting that the value of foreign key ‘Order_No.’ in the ‘equipment’ table would read automatically and that did not happen. so how to do that?

Also, when I enter row of data in the ‘equipment’ table, will I ignore the other foreign keys as they will be linked through their tables?

One Answer

Foreign Keys don't automatically generate in the child referenced tables unless you use the CASCADE specifier in the declaration of the foreign key. See Why foreign key doesn't update? for more information on how to use CASCADE with foreign keys and alternative solutions.

Correct answer by J.D. on February 22, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP