Fran Moya Fran Moya - 4 months ago 6
SQL Question

Can I control a foreign key value through MySQL?

As I've written in the tittle, I have a dubt about foreign keys on MySQL. The point is I have a class

Persona
on Doctrine and another one called
Incidencia
.

class Persona implements UserInterface, \Serializable
{
private $dni;
private $nombre;
private $apellidos;
private $telefono;
private $direccion;
private $correo;
private $codigoPostal;
private $idPuesto;
private $idGrupo;
private $username;
private $password;

/**
* @var boolean
*
* @ORM\Column(name="es_tecnico", type="boolean", nullable=false)
*/
private $esTecnico;
...
}

Class Incidencia {
private $id;
private $idTecnico;
private $idGrupo;
...
}

DROP TABLE IF EXISTS `incidencia`;
CREATE TABLE IF NOT EXISTS `incidencia` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_persona_tecnico` int(11) DEFAULT NULL,
`id_prioridad` int(11) NOT NULL,
`id_tipo_incidencia` int(11) NOT NULL,
`id_persona_peticionaria` int(11) NOT NULL,
`id_persona_afectada` int(11) NOT NULL,
`id_estado_incidencia` int(11) NOT NULL,
`id_grupo` int(11) DEFAULT NULL,
`id_ambito_afeccion` int(11) NOT NULL,
`asunto` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
`descripcion` text COLLATE utf8_unicode_ci NOT NULL,
`fecha_apertura` datetime NOT NULL,
`fecha_cierre` datetime DEFAULT NULL,
`tramitandose` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `id_persona_peticionaria` (`id_persona_peticionaria`),
KEY `id_persona_afectada` (`id_persona_afectada`),
KEY `id_prioridad` (`id_prioridad`),
KEY `id_estado_incidencia` (`id_estado_incidencia`),
KEY `id_tipo_incidencia` (`id_tipo_incidencia`),
KEY `id_ambito_afeccion` (`id_ambito_afeccion`),
KEY `id_grupo` (`id_grupo`),
KEY `id_persona_tecnico` (`id_persona_tecnico`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `incidencia`
ADD CONSTRAINT `FK_C7C6728C23BDDE75` FOREIGN KEY (`id_prioridad`) REFERENCES `prioridad` (`id`),
ADD CONSTRAINT `FK_C7C6728C628BDAE3` FOREIGN KEY (`id_grupo`) REFERENCES `grupo` (`id`),
ADD CONSTRAINT `FK_C7C6728C829BBDC6` FOREIGN KEY (`id_persona_afectada`) REFERENCES `persona` (`id`),
ADD CONSTRAINT `FK_C7C6728C8A99A9BD` FOREIGN KEY (`id_persona_peticionaria`) REFERENCES `persona` (`id`),
ADD CONSTRAINT `FK_C7C6728C8F4984FA` FOREIGN KEY (`id_estado_incidencia`) REFERENCES `estado_incidencia` (`id`),
ADD CONSTRAINT `FK_C7C6728CB2B1B14A` FOREIGN KEY (`id_ambito_afeccion`) REFERENCES `ambito_afeccion` (`id`),
ADD CONSTRAINT `FK_C7C6728CBF1EB32C` FOREIGN KEY (`id_tipo_incidencia`) REFERENCES `tipo_incidencia` (`id`),
ADD CONSTRAINT `FK_C7C6728CE3611082` FOREIGN KEY (`id_persona_tecnico`) REFERENCES `persona` (`id`);


DROP TABLE IF EXISTS `persona`;
CREATE TABLE IF NOT EXISTS `persona` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_puesto` int(11) DEFAULT NULL,
`id_grupo` int(11) DEFAULT NULL,
`id_lugar_trabajo` int(11) NOT NULL,
`id_localidad` int(11) NOT NULL,
`id_prioridad` int(11) NOT NULL,
`dni` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`nombre` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
`apellidos` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
`telefono` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
`direccion` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
`correo` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
`codigo_postal` int(11) NOT NULL,
`es_tecnico` tinyint(1) NOT NULL,
`username` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
`password` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `dni` (`dni`),
UNIQUE KEY `UNIQ_51E5B69BF85E0677` (`username`),
KEY `id_localidad` (`id_localidad`),
KEY `id_lugar_trabajo` (`id_lugar_trabajo`),
KEY `id_puesto` (`id_puesto`),
KEY `id_grupo` (`id_grupo`),
KEY `nombre` (`nombre`),
KEY `prioridad` (`id_prioridad`),
KEY `apellidos` (`apellidos`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `incidencia`
ADD CONSTRAINT `FK_C7C6728C23BDDE75` FOREIGN KEY (`id_prioridad`) REFERENCES `prioridad` (`id`),
ADD CONSTRAINT `FK_C7C6728C628BDAE3` FOREIGN KEY (`id_grupo`) REFERENCES `grupo` (`id`),
ADD CONSTRAINT `FK_C7C6728C829BBDC6` FOREIGN KEY (`id_persona_afectada`) REFERENCES `persona` (`id`),
ADD CONSTRAINT `FK_C7C6728C8A99A9BD` FOREIGN KEY (`id_persona_peticionaria`) REFERENCES `persona` (`id`),
ADD CONSTRAINT `FK_C7C6728C8F4984FA` FOREIGN KEY (`id_estado_incidencia`) REFERENCES `estado_incidencia` (`id`),
ADD CONSTRAINT `FK_C7C6728CB2B1B14A` FOREIGN KEY (`id_ambito_afeccion`) REFERENCES `ambito_afeccion` (`id`),
ADD CONSTRAINT `FK_C7C6728CBF1EB32C` FOREIGN KEY (`id_tipo_incidencia`) REFERENCES `tipo_incidencia` (`id`),
ADD CONSTRAINT `FK_C7C6728CE3611082` FOREIGN KEY (`id_persona_tecnico`) REFERENCES `persona` (`id`);


Well, the important one is
Persona.$esTecnico
. If is false, then
$username
,
$password
,
$idPuesto
and
$idGrupo
must be
NULL
. The point is that I have a foreign key attribute,
Incidencia.$idTecnico
in class
Incidencia
to
Persona
and, in my application, they only can connect if the attribute
Persona.$esTecnico
is true, so I need to control that. My doubt is if I can control that behaviour directly in MySQL (with phpmyadmin) or I need to control it with some code in my application.

Thanks!!

Answer

Try to check that provided Query:

select
person.id_persona_tecnico,
 case when person.es_tecnico = 0 then NULL else person.id_puesto end as id_puesto,
 case when person.es_tecnico = 0 then NULL else person.id_grupo end as id_grupo,
person.id_lugar_trabajo,
person.id_localidad,
person.id_prioridad,
person.dni,
person.nombre,
person.apellidos,
person.telefono,
person.direccion,
person.correo,
person.codigo_postal,
person.es_tecnico,
case when person.es_tecnico = 0 then NULL else person.username end as username,
case when person.es_tecnico = 0 then NULL else person.password end as password
 from


(select
id_persona_tecnico,
id_puesto,
id_grupo,
id_lugar_trabajo,
id_localidad,
id_prioridad,
dni,
nombre,
apellidos,
telefono,
direccion,
correo,
codigo_postal,
es_tecnico,
username,
password
 from persona)as  person



left join



(select
id_persona_tecnico,
id_prioridad,
id_tipo_incidencia,
id_persona_peticionaria,
id_persona_afectada,
id_estado_incidencia,
id_grupo,
id_ambito_afeccion,
asunto,
descripcion,
fecha_apertura,
fecha_cierre,
tramitandose
 from incidencia) as inci

on person.id_persona_tecnico = inci.id_persona_tecnico