El "ON UPDATE CASCADE ON DELETE CASCADE" no funciona

[guif]

Hola!

Tengo creada la siguiente estructura de tablas:

Código:

-- CREACION TABLA `CITIES` ( CIUDADES )
CREATE TABLE `CITIES` (
        `ID_CITY_PK` INT NOT NULL AUTO_INCREMENT,
        `CITY` VARCHAR(100),
        `LATITUDE` FLOAT (10,6) NOT NULL,
        `LONGITUDE` FLOAT (10,6) NOT NULL,
        `ID_COUNTRY` INT NOT NULL,
        PRIMARY KEY (`ID_CITY_PK`),
        FOREIGN KEY (`ID_COUNTRY`) REFERENCES `COUNTRIES`(`ID_COUNTRY_PK`) ON UPDATE CASCADE ON DELETE CASCADE
);

-- CREACION TABLA `COUNTRIES` ( PAISES )
CREATE TABLE `COUNTRIES` (
        `ID_COUNTRY_PK` INT NOT NULL AUTO_INCREMENT,
        `COUNTRY` VARCHAR(100),
        `LATITUDE` FLOAT (10,6) NOT NULL,
        `LONGITUDE` FLOAT (10,6) NOT NULL,
        `ID_CONTINENT` INT NOT NULL,
        PRIMARY KEY (`ID_COUNTRY_PK`),
        FOREIGN KEY (`ID_CONTINENT`) REFERENCES `CONTINENTS`(`ID_CONTINENT_PK`) ON UPDATE CASCADE ON DELETE CASCADE
);

-- CREACION TABLA `CONTINENTS` ( CONTINENTES )
CREATE TABLE `CONTINENTS` (
        `ID_CONTINENT_PK` INT NOT NULL AUTO_INCREMENT,
        `CONTINENT` VARCHAR(100),
        `LATITUDE` FLOAT (10,6) NOT NULL,
        `LONGITUDE` FLOAT (10,6) NOT NULL,
        PRIMARY KEY (`ID_CONTINENT_PK`)
);

-- CREACION TABLA `USERS` ( USUARIOS )
CREATE TABLE `USERS` (
        `ID_USER_PK` INT NOT NULL AUTO_INCREMENT,
        `NAME` VARCHAR(40),
        `EMAIL` VARCHAR(40),
        `PASSWORD` VARCHAR(20),
        `ID_CITY` INT NOT NULL,
        PRIMARY KEY (`ID_USER_PK`),
        FOREIGN KEY (`ID_CITY`) REFERENCES `CITIES`(`ID_CITY_PK`) ON UPDATE CASCADE ON DELETE CASCADE
);

Al realizar los inserts en el siguiente orden me funciona correctamente (me da un warning, pero creo que es por el FLOAT (10,6), pero este no es el problema)

Código:

INSERT INTO `CONTINENTS` (`ID_CONTINENT_PK`, `CONTINENT`, `LATITUDE`, `LONGITUDE`) VALUES ('','Europa','54.525961','15.255119');
INSERT INTO `COUNTRIES` (`ID_COUNTRY_PK`, `COUNTRY`, `LATITUDE`, `LONGITUDE`, `ID_CONTINENT`) VALUES ('','España','40.463667','-3.74922','');
INSERT INTO `CITIES` (`ID_CITY_PK`, `CITY`, `LATITUDE`, `LONGITUDE`, `ID_COUNTRY`) VALUES ('','Barcelona','41.387917','2.169919','');
INSERT INTO `USERS` (`ID_USER_PK`,`NAME`,`EMAIL`,`PASSWORD`,`ID_CITY`) VALUES ('', 'Manuel Fernandez','[email protected]','123456','Barcelona');

El Problema es que el ON UPDATE CASCADE ON DELETE CASCADE no se modifican en las tablas correspondientes y me aparece siempre un 0.
Alguna idea?

[gnzsoloyo]

El Problema es que el ON UPDATE CASCADE ON DELETE CASCADE no se modifican en las tablas correspondientes y me aparece siempre un 0.
Alguna idea?

Lo que estás haciendo son INSERTS, no UPDATES, por lo cual el evento controlado por ON UPDATE CASCADE no se produce.
El sentido y función de un ON UPDATE CASCADE ON DELETE CASCADE es que si se modifica la PK de la tabla primaria, se actualice en las tablas dependientes ese mismo valor para todos los registros relacionados; no significa que si insertas un registro en la primaria, este se inserte en la secundaria. Eso no existe.
Para que sea más claro: Si has insertado el país Nro. 4 en PAISES, e insertado una ciudad en CIUDADES que has relacionado con ese páis 4, si por alguna razón decides cambiar el ID 4 por 235, MySQL automáticamente buscará todos los registros de ciudades que tengan el ID de país en 4 y lo reemplazará por 235. Peor no creará nuevos registros.
¿Eso lo entiendes, no?

Por otro lado, si estás operando correctamente, pero usando tablas MyISAM, estas no poseen FK, por lo que el ON DELETE... ON UPDATE es inoperante.

[guif]

Si. Como bien dices, me gustaria que se actualizara en las siguientes tablas (Tanto si es insert, como update,...). Que debería entonces de modificar?

[gnzsoloyo]

Aclarame esto:

¿Qué tablas estás utilizando?: InnoDB o MyISAM

¿Quieres que actualice con un INSERT? Un INSERT no puede llevar más información que la propia de la tabla donde se insertarán los datos, por lo que no puedes enviar un INSERT de país que al mismo tiempo realice un INSERT de ciudades.
Esos son dos INSERT separados, y no puedes insertar un registro en ciudades sin insertar primero el país a que pertenece. Eso es parte de la integridad referencial del modelo.

Lo que tienes que hacer es primero dar de alta a los países y luego preocuparte por darle de alta a las ciudades...

¿Cuál es el problema que tienes para hacerlo de ese modo? Si lo explicas podemos encontrar la forma correcta de lograrlo...

[guif]

Tienes razon. Me he liado con la integridad referencial y lo estoy reestructurando.
Evidentemente, antes de añadir un Usuario las otras tablas ya estarian rellenadas.
Sobre lo de las tablas que me comentas (InnoDB o MyISAM) desconozco el tema...

[gnzsoloyo]

Sobre lo de las tablas que me comentas (InnoDB o MyISAM) desconozco el tema...

El tema es simple:
MySQL puede usar diferentes tipos de motores de tablas subyacentes. Desde la versión 5.0.x usa InnoDB como motor por default, pero hay algunas versiones (académicas, creo) que tienen activado por default el MyISAM. La diferencia esencial radica en que las InnoDB soportan transaccciones e integridad referencial y las MySAM no (ni lo uno ni lo otro).
Esta diferencia es crítica porque tu no estás indicando en el script cuál ENGINE usarás, por lo que si lo ejecutaras en un server MySQL con MyISAM por default no tendrás jamás control de FK.
En las tablas MyISAM es el programador el que debe cuidar la integridad referencial porque el DBMS no le va a avisar nada cuando se produzca una violación de la misma, porque no lo detecta. Tampoco puedes crear ni administrar transacciones, lo que hace que haya más programación en el asunto.
Las tablas MyISAM son muy usadas en la web por la rapidez de inserciones/actualizaciones/borrados, pero no sirven para modelo de datos de cierta complejidad como el que quieres.

Revisa las variables locales del server: Allí sale qué motor estás usando por default:

Código MySQL:

mysql> SELECT @@have_innodb;
+---------------+
| @@have_innodb |
+---------------+
| YES           |
+---------------+
1 row in set (0.00 sec)
 

[guif]

revisare el tema y si tubiera alguna duda os lo comenta.

gracias por la explicacion!

[lichoac]

Hola gente como estan?

Tengo un problema con el tema de las Foreign para hacer un ON UPDATE CASCADE

La cuestion es que tengo un sistemita que nunca borra registros, cuando alguien ejecuta la opcion borrar lo que hago es modificar el valor del campo "estado" y le pongo valor = 2. Uso siempre 1 para activo, 2=borrado.

Ahora necesito hacer un poco de integridad referencial y me gustaria que on update cascade cuando el campo "estado" (de la tabla padre) pasa a tener valor = 2, tamben se modifique el campo "estado" de la/las tablas hijas al valor = 2.

Es posible?
No encuentro alguna documentacion que me sirva para este caso.

Desde ya muchas gracias!! Saludos!

[gnzsoloyo]

No es posible en el contexto de un ON UPDATE, porque el ON UPDATE ... ON DELETE... sólo opera contra campos que son FOREIGN KEY y por tanto dependen de una PRIMARY KEY a que se refieren. No sirven para hacer operaciones contra campos que no son PK en su tabla de origen.

Lo que tú necesitas es manejar ese problema con TRIGGERS, para lo cual lo primero sería qu ele dieses una leída al tema desde la óptica de MySQL: 20 Disparadores (triggers)


P.D.: No es buena costumbre colgarse de un post ajeno para preguntar cosas propias. No es gentil. Uno abre los temas por el primer post, no por el último...
Además corres riesgo de hacerlo de un post muy viejo y que lo cancelen (resucitar temas viejos está prohibido, Punto 2.7 - Políticas de Foros del Web).

[lichoac]

Gracias!

Perdon que pregunte en un post que no era mio, pero como es del mismo "tema" que mi pregunta me parecio adecuado.

Muy gentil voy a leer de triggers ahora mismo

saludos!