How to capture more than one attribute from a foreign key?

Question

I’m doing a Java-Desktop Vehicle Rental project. I’m using MVC, DAO and JDBC.

In my bank I have the tables Rental and Car:

CREATE TABLE IF NOT EXISTS `frota`.`carro` (
  `idcarro` INT(11) NOT NULL AUTO_INCREMENT,
  `chassi` VARCHAR(45) NULL DEFAULT NULL,
  `renavam` VARCHAR(45) NULL DEFAULT NULL,
  `placa` VARCHAR(45) NULL DEFAULT NULL,
  `combustivel` VARCHAR(45) NULL DEFAULT NULL,
  `numero_de_portas` INT(11) NULL DEFAULT NULL,
  `cor` VARCHAR(45) NULL DEFAULT NULL,
  `ano` INT(11) NULL DEFAULT NULL,
  `quilometragem` INT(11) NULL DEFAULT NULL,
  `valor_locacao` DOUBLE NULL DEFAULT NULL,
  `marca` VARCHAR(100) NULL DEFAULT NULL,
  `modelo` VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (`idcarro`))
ENGINE = InnoDB
AUTO_INCREMENT = 19
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `frota`.`locacao` (
  `idlocacao` INT(11) NOT NULL AUTO_INCREMENT,
  `fkcodcarro` INT(11) NULL,
  `fkcodcliente` INT(11) NULL,
  `fkcodfuncionario` INT(11) NULL,
  `data_locacao` DATE NULL DEFAULT NULL,
  `hora_locacao` TIME NULL DEFAULT NULL,
  `data_devolucao` DATE NULL DEFAULT NULL,
  `hora_devolucao` TIME NULL DEFAULT NULL,
  `tempo` VARCHAR(45) NULL DEFAULT NULL,
  `status` VARCHAR(100) NOT NULL,
  `taxa` DOUBLE NOT NULL,
  PRIMARY KEY (`idlocacao`),
  INDEX `fk_locacao_funcionario1_idx` (`fkcodfuncionario` ASC),
  INDEX `fk_locacao_cliente1_idx` (`fkcodcliente` ASC),
  INDEX `fk_locacao_carro1_idx` (`fkcodcarro` ASC),
  CONSTRAINT `fk_locacao_carro1`
    FOREIGN KEY (`fkcodcarro`)
    REFERENCES `frota`.`carro` (`idcarro`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_locacao_cliente1`
    FOREIGN KEY (`fkcodcliente`)
    REFERENCES `frota`.`cliente` (`idcliente`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_locacao_funcionario1`
    FOREIGN KEY (`fkcodfuncionario`)
    REFERENCES `frota`.`funcionario` (`idfuncionario`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 15
DEFAULT CHARACTER SET = utf8;

My intention is to record in the bank on the Leasing table. I know I have to use the foreign car key(fkcodcarro) somehow, I have researched and tried to do several ways but always keeps giving the same error.

Answer 1

There are some properties that all relational databases should respect, are the ACID properties:

• Atomicity: all steps of a transaction must occur. If one step does not occur, no other occurs.
• Consistency: all customers see the same result after a transaction.
• Isolation: a transaction must occur without side effects.
• Durability: the result of a transaction is persisted.

That way, I believe the transaction you are using to insert a row into the table locacao is breaking some of these properties. To check which point is the error, it is worth asking the following questions:

• I’m trying to insert something null into a restricted field NOT NULL?
• I’m trying to insert some kind of data into some field that’s not compatible with this type?
• Am I violating some primary-key constraint? Pay attention to the values that are AUTO INCREMENT
• Am I violating any foreign-key restrictions? Check if the foreign key of the line you want to enter matches a record that actually exists in the foreign table.

I hope these dots help you figure out where your mistake is.