[SOLUCIONADO] Error al relacionar claves foraneas en Clave compuesta

[dpruebas]

Un saludo,

Tengo tablas en una relación N:N así (subrayado es clave primaria):

primeratabla(idtabla1, otrocampo, yotro)
segundatabla(idtabla2,uncampomas,unomas)
relacion-primeratabla-segundatabla(idtabla1,idtabla2)

El problema es que no consigo hacer las relaciones de las llaves foraneas relacion-primeratabla-segundatabla con los id respectivos, el problema radica en no poder hacer los CONSTRAINT (ON DELETE & ON UPDATE).

Cuando lo intento, únicamente me deja poner UN CONSTRAINT en una clave foránea y ya en la segunda no me lo permite. Puedo hacerlo con ambas, es decir, ponerla a idtabla1 y ya no me deja a idtabla2, o ponerla a idtabla2 y ya no me deja a idtabla1.

• He probado por interfaz (phpMyAdmin) en ordenes diferentes.
• He probado creando la tabla sin claves y luego con otra consulta SQL ponerlas, o crear la tabla con indices (INDEX) únicamente y luego lo demás, o crear la tabla con claves foráneas, y todas las combinaciones posibles.
• Alguna vez he conseguido hacerlo, pero al hacer una prueba (como actualizar un ON DELETE a RESTRIC en vez de CASCADE) desde phpMyAdmin, vuelve y arroja un error y borra toda la cuestión (Pone en blanco el ON DELETE y el ON UPDATE).

No entiendo porque no puedo, si tengo otras bases de datos donde lo he hecho (igual, mismo CHARSET (utf-8), mismo COLLATE(utf8_spanish_ci), mismo motor (INNODB)).

Llevo varios días buscando y probando, ya no tuve más opción.

Muchas gracias.

[gnzsoloyo]

Empecemos por el principio: Postea la sentencia con que quieres agregar esas constraint, y en especial postea el CREATE TABLE de cada una de las tres tablas.
Anticipándome a tu respuesta, debes repasar la lista de condiciones necesarias y obligatorias para poder definir una FK:
1) La FK sólo puede apuntar a una PK, y en MySQL puede (alternativamente), hacerlo a un campo definido como UNIQUE.
2) El campo usado como FK deben ser del mismo tipo, longitud y collation (si es de caracteres) que el campo PK de la tabla referida.
3) SI una KF apunta a una tabla cuya PK es compuesta (más de un campo en la definición de la PK), la FK debe ser creada con la misma cantidad de campos, y en el mismo orden de la PK referida.
4) Los charset de las tablas deben ser idénticos.
5) Todas las tablas deben existir en el mismo esquema de bases de datos.

Y finalmente, un detalle poco verificado:
6) Si estás intentando definir una FK en una tabla que ya contiene registros, debes verificar que no existan inconsistencias entre los datos que la tabla contiene, respecto de la FK que estás tratando de definir.
Esto último implica que si hay al menos un registro que contenga un valor nulo, o que no exista entre las PK de la tabla que se referenciará, MySQL no te admitirá crear la FK.
Siempre que en un DBMS se crea una FK, el sistema verifica que los datos existentes cumplan con la constraint a crear. Siempre.

[dpruebas]

Gracias por tu respuesta.

Te resumo como quiero que quede con el nivel de detalle posible:

primeratabla
idtabla1 - int UNSIGNED AUTOINCREMENT - PRIMARY
otrocampo
yotro

segundatabla
idtabla2 - smallint(3) UNSIGNED AUTOINCREMENT - PRIMARY
uncampomas
unomas

relacion-primeratabla-segundatabla
idtabla1 -> primeratabla.idtabla1 ON DELETE RESTRICT ON UPDATE CASCADE - int UNSIGNED - FOREIGN
idtabla2 -> segundatabla.idtabla2 ON DELETE RESTRICT ON UPDATE CASCADE - smallint(3) UNSIGNED - FOREIGN
[Juntas son la llave primaria: idtabla1,idtabla2 - PRIMARY]

Como te comentaba, he usado muchas combinaciones para hacerlo:

• Crear la tabla con las llaves foráneas y primaria.
• Crear la tabla (relacion-primeratabla-segundatabla) sin llaves. Luego crear las llaves.
• etc.

Asumiento lo anterior, y que las tablas son sencillas, no se si sería inapropiado preguntarte ¿como crearías tu la tabla y las relaciones en MySQL? (Sería crear la tabla 'relacion-primeratabla-segundatabla' y sus relaciones, las otras dos tablas no tienen problema.)

Sobre las condiciones que comentas, todas se cumplen.
Aunque la 3. no estoy seguro de entenderla muy bien; entiendo que si quisiera apuntar a una llave primaria compuesta (ejp: idtabla1,idtabla2) debo hacerlo con ambos campos (iguales, tipo y todo eso). Entendiéndolo así no creo querer esto, sino lo inverso, apuntar cada campo de la llave a su correspondiente tabla y llave primaria.

Gracias de antemano.

[gnzsoloyo]

Vamos de vuelta: Las descripciones como la que pones, no nos sirven mucho. en realidad no sirve casi de nada, porque se omite información importante.
Cuando te pido el CREATE TABLE es exactamente eso lo que pido... No un descriptivo.
Básicamente: No me sirve que me digas cómo lo quieres, sino lo que tienes ahora.

¿como crearías tu la tabla y las relaciones en MySQL?

Eso no existe. No es cómo yo las crearía, sino cómo se hace. Se hace siempre de la misma forma, sin importar cuál es el diseño.

Haz lo siguiente: Suponiendo que las tablas se llamen "primera", "segunda" y "relacion_primera_segunda":

Código MySQL:

SHOW CREATE TABLE primera;
SHOW CREATE TABLE segunda;
SHOW CREATE TABLE relacion_primera_segunda;
 

Luego, copia el resultado de cada una de ellas y postealo acá, junto con la sentencia ALTER TABLE usada por tí para intentar crear la constraint
Haz eso, y luego seguimos.

Y si no puedes o no quieres aportar esto que te pido, al menos dime por qué, ya que lo que te estoy planteando es por razones concretas: Se necesita para entender el problema y realizar una prueba de verificación.

Respecto al punto 3, si te fijas, te estoy poniendo una lista de requisitos,. No estoy diciendo que los estés necesitando, sino solamente explico cuáles son las posibilidades. COmo no nos estas dando el CREATE TABLE, no puedo estar seguro de si es el caso o no, y no sería la primera vez que alguien omita una información esencial como esa.

[dpruebas]

Haciendo de nuevo las pruebas, concluyo:

* Previo (tablas iniciales):

Código MySQL:

CREATE TABLE `primeratabla` (
`idtabla1` int unsigned NOT NULL auto_increment,
`otrocampo` varchar(10) NOT NULL,
`yotro` varchar(10) NOT NULL,
PRIMARY KEY (`idtabla1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
 
CREATE TABLE `segundatabla` (
`idtabla2` smallint(3) unsigned NOT NULL auto_increment,
`uncampomas` varchar(10) NOT NULL,
`unomas` varchar(10) NOT NULL,
PRIMARY KEY (`idtabla2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
 

* Funciona:
Cuando la consulta de las llaves foráneas se hace en conjunto.
Ya sea creando la tabla:

Código MySQL:

CREATE TABLE `relacion-primeratabla-segundatabla` (
`idtabla1` int unsigned NOT NULL,
`idtabla2` smallint(3) unsigned NOT NULL,
FOREIGN KEY ( `idtabla1` ) REFERENCES `primeratabla` (`idtabla1`) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY ( `idtabla2` ) REFERENCES `segundatabla` (`idtabla2`) ON DELETE RESTRICT ON UPDATE CASCADE,
PRIMARY KEY (`idtabla1`,`idtabla2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
 

O modificandola:

Código MySQL:

ALTER TABLE `relacion-primeratabla-segundatabla` 
ADD FOREIGN KEY ( `idtabla1` ) REFERENCES `primeratabla` (`idtabla1`) ON DELETE RESTRICT ON UPDATE CASCADE,
ADD FOREIGN KEY ( `idtabla2` ) REFERENCES `segundatabla` (`idtabla2`) ON DELETE RESTRICT ON UPDATE CASCADE,
ADD PRIMARY KEY (`idtabla1`,`idtabla2`)
 

NOTA: es indiferente dónde se cree la llave primaria (en consulta aparte o en la misma).

* No funciona:
Modificando las llaves foráneas por separado:

Código MySQL:

ALTER TABLE  `relacion-primeratabla-segundatabla` ADD FOREIGN KEY ( `idtabla1` ) REFERENCES `primeratabla` (`idtabla1`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE  `relacion-primeratabla-segundatabla` ADD FOREIGN KEY ( `idtabla2` ) REFERENCES `segundatabla` (`idtabla2`) ON DELETE RESTRICT ON UPDATE CASCADE;
 

Por lo tanto tampoco funciona por la interfaz gráfica de phpMyAdmin ya que en la misma para estas acciones siempre usa la consulta que las modifica por separado.

¿Por qué?
Eso es lo que no sé, pero como el problema está resuelto lo marco como tal.

gnzsoloyo, eres muy amable en responder estas preguntas en los foros (he visto muchas de tus respuestas), y estructuras las mismas de forma muy clara... pero te comento, solo si lo quieres tener en cuenta (no para discutirlo), no eres nada amable en la forma como respondes, da la impresión de que regañas, que te las sabes todas y mereces un busto, y por más útiles que sean tus respuestas, y por más conocimiento que tengas, lo agradable de un foro es que quién responde no solo siempre está dispuesto (un foro es eso, un mundo de dudas), no solo comparte todo lo que sabe, lo hace porque le gusta y en definitiva suelen ser muy amables, al menos en las primeras respuestas, sobre todo con los novatos, algo que todos somos o fuimos. A pesar de esto, no quiero prejuzgar sin conocerte, mas bien agradecerte por la rapidez con la que respondes. Espero que no te lo tomes a mal y que pena contigo haber planteado así el problema, entiendo que no fui claro.

[gnzsoloyo]

Bueno, gracias por finalmente poner las sentencias de creación. Ahora algunas cosas resultan más claras.
Por lo pronto, el esquema de creación de todas las tablas, incluyendo las FK en la definición de la última, funciona bien:

Código MySQL:

mysql>
mysql> CREATE TABLE `primeratabla` (
    -> `idtabla1` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `otrocampo` VARCHAR(10) NOT NULL,
    -> `yotro` VARCHAR(10) NOT NULL,
    -> PRIMARY KEY (`idtabla1`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
Query OK, 0 rows affected (0.01 sec)
 
mysql>
mysql> CREATE TABLE `segundatabla` (
    -> `idtabla2` SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `uncampomas` VARCHAR(10) NOT NULL,
    -> `unomas` VARCHAR(10) NOT NULL,
    -> PRIMARY KEY (`idtabla2`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
Query OK, 0 rows affected (0.01 sec)
 
mysql>
mysql> CREATE TABLE `relacion-primeratabla-segundatabla` (
    -> `idtabla1` INT UNSIGNED NOT NULL,
    -> `idtabla2` SMALLINT(3) UNSIGNED NOT NULL,
    -> FOREIGN KEY ( `idtabla1` ) REFERENCES `primeratabla` (`idtabla1`) ON DELETE RESTRICT ON UPDATE CASCADE,
    -> FOREIGN KEY ( `idtabla2` ) REFERENCES `segundatabla` (`idtabla2`) ON DELETE RESTRICT ON UPDATE CASCADE,
    -> PRIMARY KEY (`idtabla1`,`idtabla2`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
Query OK, 0 rows affected (0.06 sec)
 

Evidentemente, eso no tiene problemas de tipo de dato, o de sintaxis.
hacerlo en dos etapas requiere, como es evidente, no definirle más que la PK a la tercera tabla desde inicio:

Código MySQL:

mysql> CREATE TABLE `primeratabla` (
    -> `idtabla1` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `otrocampo` VARCHAR(10) NOT NULL,
    -> `yotro` VARCHAR(10) NOT NULL,
    -> PRIMARY KEY (`idtabla1`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
Query OK, 0 rows affected (0.01 sec)
 
mysql>
mysql> CREATE TABLE `segundatabla` (
    -> `idtabla2` SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `uncampomas` VARCHAR(10) NOT NULL,
    -> `unomas` VARCHAR(10) NOT NULL,
    -> PRIMARY KEY (`idtabla2`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
Query OK, 0 rows affected (0.01 sec)
 
mysql>
mysql> CREATE TABLE `relacion-primeratabla-segundatabla` (
    -> `idtabla1` INT UNSIGNED NOT NULL,
    -> `idtabla2` SMALLINT(3) UNSIGNED NOT NULL,
    -> PRIMARY KEY (`idtabla1`,`idtabla2`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
Query OK, 0 rows affected (0.01 sec)
 
mysql>
mysql> ALTER TABLE `relacion-primeratabla-segundatabla`
    -> ADD FOREIGN KEY ( `idtabla1` ) REFERENCES `primeratabla` (`idtabla1`)
    -> ON DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql>
mysql> ALTER TABLE  `relacion-primeratabla-segundatabla`
    -> ADD FOREIGN KEY ( `idtabla2` ) REFERENCES `segundatabla` (`idtabla2`)
    -> ON DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
 

Tampoco causa problemas, por lo cual no se ve defectos en ningún caso.
Francamente, llegado a este punto sólo puedo suponer que el problema está en la versión del phpMyadmin, o algo semejante, ya que al no ser parte de MySQL (es una interfaz programada en PHP), y realizar internamente la creación de las sentencias que envía a MySQL, hay un margen de error probable.
Como sea, ni la definición de las tablas está mal, ni tampoco las de las FK.

Finalmente, es posible que haya resultado demasiado áspera mi reacción, el problema es que me cansa sobremanera pedir específicamente que posteen el CREATE TABLE, para que me terminen contestando con descripciones vagas, que ocultan mas cosas de las que aclaran.
Lo siento si fue excesivo. En algún momento me sacan de las casillas, porque es como si no leyesen lo que se les pide.

Como sea, mis disculpas.

Volviendo al tema, hay sí un par de consejos para que tengas en cuenta:
1) Nunca uses caracteres reservados en los nombres de objetos de bases de datos. No se deben usar dentro de los nombres ni paréntesis, si signos aritméticos, porque en algún momento, por más precauciones que tengas, se producirá un error sintáctico a causa de ellos.
2) No pongas el "rango" de un tipo de dato numérico entero, sólo sirven en el DECIMAL. Ese "(3)" que le pusiste al SMALLINT no genera ningún tipo de efecto al rango de representación, y puede causarte problemas luego si usas VIEWs. Ese valor numérico no representa el ancho de las cifras. Ponerlo, especialmente cuando es menor que el real, lo único que hará es confundirte y crearte problemas en el futuro.