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

0

I'm doing a Java-Desktop Vehicle Lease project. I am using MVC, DAO and JDBC.

In my bank I have the Rental and Car tables:

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 write to the bank in the Location table. I know I have to use the car foreign key (fkcodcarro) somehow, I already researched and tried to do it in several ways but it always keeps giving the same error.

    
asked by anonymous 01.02.2017 / 16:13

1 answer

0

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

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

In this way, I believe that the transaction you are using to insert a row in the locacao table is breaking some of these properties. To check where the error is, it's worth asking the following questions:

  • I'm trying to insert something null in a field with NOT NULL ?
  • I am trying to insert a data type into some field that is not compatible with this type?
  • Am I violating any primary key constraints? Pay attention to the values that are AUTO INCREMENT
  • Am I violating some foreign key constraint? Verify that the foreign key of the row you want to insert corresponds to a record that actually exists on the foreign table.

I hope these points help you figure out where your error is.

    
01.02.2017 / 17:27