Hellos…
Can someone please help me how to update the data in vehicle table ( i.e. the recordId column, when inserting data into accidentrecord), assuming that data in vehicles where already present in both drivers and vehicle table?
CREATE TABLE IF NOT EXISTS `mimi`.`accidentrecord` (
`recordId` SMALLINT(6) NOT NULL AUTO_INCREMENT ,
`date_of_occurance` DATE NULL DEFAULT NULL ,
`timeofday` VARCHAR(10) NULL ,
`weather` VARCHAR(10) NULL DEFAULT NULL ,
`location` VARCHAR(45) NULL ,
`numberOfVehicles` SMALLINT(6) NULL DEFAULT NULL ,
PRIMARY KEY (`recordId`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `mimi`.`drivers` (
`driverID` SMALLINT(6) NOT NULL AUTO_INCREMENT ,
`schoolId` SMALLINT(6) NOT NULL ,
`driverRegNo` VARCHAR(45) NULL ,
`surname` VARCHAR(45) NULL DEFAULT NULL ,
`othernames` VARCHAR(45) NULL DEFAULT NULL ,
`DOB` DATE NULL DEFAULT NULL ,
`address` TEXT NULL DEFAULT NULL ,
`gender` CHAR(1) NULL DEFAULT NULL ,
`placeOfBirth` VARCHAR(15) NULL DEFAULT NULL ,
`telephoneNo` VARCHAR(45) NULL DEFAULT NULL ,
PRIMARY KEY (`driverID`) ,
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
CREATE TABLE IF NOT EXISTS `mimi`.`vehicles` (
`vehicleID` SMALLINT(6) NOT NULL AUTO_INCREMENT ,
`recordId` SMALLINT(6) NULL ,
`driverID` SMALLINT(6) NOT NULL ,
`vehicleNo` VARCHAR(45) NULL ,
`insuranceNo` VARCHAR(45) NULL ,
`vehicleType` VARCHAR(45) NULL DEFAULT NULL ,
`ownership` VARCHAR(45) NULL DEFAULT NULL ,
PRIMARY KEY (`vehicleID`) ,
INDEX `fk_vehicles_accidentrecord1` (`recordId` ASC) ,
INDEX `fk_vehicles_drivers1` (`driverID` ASC) ,
CONSTRAINT `fk_vehicles_accidentrecord1`
FOREIGN KEY (`recordId` )
REFERENCES `mimi`.`accidentrecord` (`recordId` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_vehicles_drivers1`
FOREIGN KEY (`driverID` )
REFERENCES `mimi`.`drivers` (`driverID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;