Prevent the insertion by the user of the same number of CPF in three different fields

Asked

Viewed 68 times

3

In a table I carry the number of patient, doctor and attendant. I’m trying to stop the person from scheduling the 3 with the same values. Like:

INSERT INTO agenda(cpf_atendente, cpf_paciente, cpf_medico, dataAgendamento) 
     VALUES(1837857227, 1837857227, 1837857227, '2020-03-05');

But I didn’t succeed.

CREATE TABLE IF NOT EXISTS `agenda` (
  `cpf_atendente` INT(11) NOT NULL,
  `cpf_paciente` INT(11) NOT NULL, 
  `cpf_medico` INT(11) NOT NULL,
  `dataAgendamento` DATE NOT NULL,      
  PRIMARY KEY (`cpf_atendente`,`cpf_paciente`,`cpf_medico`,`dataAgendamento`),
   UNIQUE INDEX `unico`(`cpf_paciente`,`cpf_medico`, `cpf_atendente`),
    FOREIGN KEY (`cpf_atendente`)
    REFERENCES `atendente` (`cpf_atendente`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    FOREIGN KEY (`cpf_paciente`)
    REFERENCES `paciente` (`cpf_paciente`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    FOREIGN KEY (`cpf_medico`)
    REFERENCES `medico` (`cpf_medico`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
mysql sql database
  • 1

    Inform the DBMS concerned, the solution may be a check Constraint or validation Trigger.

  • It’s Mysql I’m using

  • 1

    I would do differently, create a field called cod_key int and primary_key autoincrement, then create Index Unique with the three cpf_patient, cpf_medic and cpf_attendant fields, would not control with the field dateAgendering because it very changeable... the control of the dateAgendering can be done in the application, because you can simply consult if at that date and time there is already scheduling for a certain doctor, the guy won’t be able to meet 2 at the same time... I don’t know the application, but I think logic is getting in your way... I have a scheduling system and did as above.

1 answer

5


As @Motta suggested, one possible solution is to perform control through Trigger.

CREATE TRIGGER ValidaTresCPF BEFORE INSERT ON Agenda
FOR EACH ROW
begin
    DECLARE msg VARCHAR(255);
    IF (new.cpf_atendente = new.cpf_paciente) and (new.cpf_atendente = new.cpf_medico) THEN
        set msg = "Os 3 CPFs não podem ser iguais.";
        SIGNAL SQLSTATE '45003' SET MESSAGE_TEXT = msg;
   END IF;
END;

SIGNAL is the way to "return" an error. SIGNAL provides error information to a handler, to an external part of the application or client. In addition, provides control over the error characteristics (error number, SQLSTATE value, message).

This way, in addition to avoiding insertion, returns an error message that can be handled in the application.

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.