0

Hi I'm trying to create a unique key composed in MySQL, so far there is no problem, my problem comes when one of the fields included in this unique key allows null at this point the only key fails because it allows to be saved identical values in the fields covered by the only key containing null.

This is my table.

    CREATE TABLE `pacienteprocedures` (
    `Id` INT(11) NOT NULL AUTO_INCREMENT,
    `Fecha` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `Paciente` BIGINT(15) NOT NULL,
    `Diente` INT(11) NULL DEFAULT NULL,
    `Zone` INT(11) NOT NULL,
    `Procedure` INT(11) NOT NULL,
    `Tipe` INT(11) NOT NULL,
    PRIMARY KEY (`Id`),
    UNIQUE INDEX `Paciente` (`Paciente`, `Diente`, `Zone`, `Procedure`),
    INDEX `FK_PacienteDiagnosticos_paciente` (`Paciente`),
    INDEX `FK_PacienteDiagnosticos_dientes` (`Diente`),
    INDEX `FK_PacienteDiagnosticos_diagnosticos` (`Procedure`),
    INDEX `FK_pacienteprocedures_zones` (`Zone`),
    INDEX `FK_pacienteprocedures_tipeitems` (`Tipe`),
    CONSTRAINT `FK_PacienteDiagnosticos_diagnosticos` FOREIGN KEY (`Procedure`) REFERENCES `items` (`Id`) ON UPDATE CASCADE,
    CONSTRAINT `FK_PacienteDiagnosticos_dientes` FOREIGN KEY (`Diente`) REFERENCES `dientes` (`Id`) ON UPDATE CASCADE,
    CONSTRAINT `FK_PacienteDiagnosticos_paciente` FOREIGN KEY (`Paciente`) REFERENCES `pacientes` (`Id`) ON UPDATE CASCADE,
    CONSTRAINT `FK_pacienteprocedures_tipeitems` FOREIGN KEY (`Tipe`) REFERENCES `tipeitems` (`Id`) ON UPDATE CASCADE,
    CONSTRAINT `FK_pacienteprocedures_zones` FOREIGN KEY (`Zone`) REFERENCES `zones` (`Id`) ON UPDATE CASCADE
) COLLATE='utf8_spanish2_ci' ENGINE=InnoDB AUTO_INCREMENT=92;

When inserting the following

INSERT INTO `odontograma1`.`pacienteprocedures` (`Paciente`, `Zone`, Procedure`, `Tipe`) VALUES (1104, 11, 1, 1);
INSERT INTO `odontograma1`.`pacienteprocedures` (`Paciente`, `Zone`, `Procedure`, `Tipe`) VALUES (1104, 11, 1, 1);

Because the Diente null field will not be inserted in both cases, so that the fields remain the same and this is the problem, I can not allow this to happen.

Anyone can help, I thank you.

Carlos Andres Guzman Arreola
  • 69
  • 1
  • 8
  • 1
    Are you sure this is an issue? I tried CREATE TABLE (without FK) and two INSERTs. It was fine and no error. – Tin Apr 26 '16 at 21:54
  • Hello, the code does not throw me any errors, insertions either, my problem is I can not allow insert two or more records with fields Paciente, Diente, Zone, Procedure equal. – Carlos Andres Guzman Arreola Apr 26 '16 at 22:03
  • Inserts that show above should not be done, should only allow inserting the first, the second should show an error. – Carlos Andres Guzman Arreola Apr 26 '16 at 22:05
  • I don't know much about what systems are you designing for. I can think of two possible ways that you can fix. Would it make sense to you? I'm not so sure. 1) Have 3 composite keys instead of 4 like `(Paciente, Diente, Zone, Procedure)` -> only 3 without Diente `(Paciente, Zone, Procedure)` because Diente seems not quite important 2) Have a default entry (like N/A or some value) for Diente and make it NOT NULL. I think #2 might be better approach for you. – Tin Apr 27 '16 at 15:22
  • Hello, the only way I found was controlled from the code in this particular case php – Carlos Andres Guzman Arreola Apr 29 '16 at 21:14

0 Answers0