Problemas creando relación M:N

Bernardao · #1 (**permalink**) 26/07/2011, 14:01

Hola,
Estoy intentando relacionar dos tablas, jugadores y equipos, una relación M:N
Para esto he creado una nueva tabla Equipo_Jugadores. Abajo está el sql para crearlas, las he diseñado con Workbench, cuando ejecuto el sql en phpmyadmin, me aparece el siguiente error:

MySQL ha dicho:
#1005 - Can't create table 'futbol.equipo_jugadores' (errno: 121)

Muchas gracias, si alguien me puede ayudar.

----------------TABLAS--------------

CREATE TABLE IF NOT EXISTS `futbol`.`Jugadores` (
`idJugador` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`equipo` INT NULL ,
`nombre` VARCHAR(45) NULL ,
`apellido` VARCHAR(45) NULL ,
PRIMARY KEY (`idJugador`) ,
UNIQUE INDEX `idJugadores_UNIQUE` (`idJugador` ASC) ,
INDEX `equipo` (`equipo` ASC) ,
CONSTRAINT `equipo`
FOREIGN KEY (`equipo` )
REFERENCES `futbol`.`Equipo_Jugadores` (`equipo` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `futbol`.`Equipos` (
`idEquipo` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`nombre` VARCHAR(45) NOT NULL ,
`jugador` INT NULL ,
`categoria` INT NULL ,
PRIMARY KEY (`idEquipo`) ,
UNIQUE INDEX `idEquipos_UNIQUE` (`idEquipo` ASC) ,
INDEX `categoria` (`categoria` ASC) ,
INDEX `jugador` (`jugador` ASC) ,
CONSTRAINT `categoria`
FOREIGN KEY (`categoria` )
REFERENCES `futbol`.`Categoria` (`idCategoria` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `jugador`
FOREIGN KEY (`jugador` )
REFERENCES `futbol`.`Equipo_Jugadores` (`jugador` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `futbol`.`Equipo_Jugadores` (
`idEquipo_Jugadores` INT NOT NULL AUTO_INCREMENT ,
`equipo` INT NULL ,
`jugador` INT NULL ,
PRIMARY KEY (`idEquipo_Jugadores`) ,
UNIQUE INDEX `idEquipo_Jugadores_UNIQUE` (`idEquipo_Jugadores` ASC) ,
INDEX `jugador` (`jugador` ASC) ,
INDEX `equipo` (`equipo` ASC) ,
CONSTRAINT `jugador`
FOREIGN KEY (`jugador` )
REFERENCES `futbol`.`Jugadores` (`idJugador` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `equipo`
FOREIGN KEY (`equipo` )
REFERENCES `futbol`.`Equipos` (`idEquipo` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

gnzsoloyo · #2 (**permalink**) 26/07/2011, 16:10

Por lo pronto, has creado relaciones circulares, que son relaciones imposibles de administrar. Esto se da porque estás poniendo interdependencias que hace imposible crear las tablas tal y como lo planteas.
Además, ten en cuenta que la PK de una relación N:N está compuesta por las PK correspondientes a las tablas de origen, y sólo necesita un discriminante si puede darse que haya más de un par idéntico relacionado, que no es el caso.
Las FK no son transitivas, por lo que las tablas base no necesitan de la FK de laotra tabla, que ya está en el origen.
Otra cosa: Te olvidaste de una tabla (categorías), sin la cual el esquema no se puede crear.
El esquema sería mas o menos así:

Código MySQL:

Ver originalDROP SCHEMA IF EXISTS `futbol` ;
CREATE SCHEMA IF NOT EXISTS `futbol` DEFAULT CHARACTER SET latin1 ;
 
-- -----------------------------------------------------
-- Table `futbol`.`equipos`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `futbol`.`equipos` ;
 
CREATE  TABLE IF NOT EXISTS `futbol`.`equipos` (
  `idEquipo` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `nombre` VARCHAR(45) NOT NULL ,
  `categoria` INT(11) NULL DEFAULT NULL ,
  PRIMARY KEY (`idEquipo`) ,
  INDEX `categoria` (`categoria` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
 
 
-- -----------------------------------------------------
-- Table `futbol`.`jugadores`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `futbol`.`jugadores` ;
 
CREATE  TABLE IF NOT EXISTS `futbol`.`jugadores` (
  `idJugador` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `nombre` VARCHAR(45) NULL DEFAULT NULL ,
  `apellido` VARCHAR(45) NULL DEFAULT NULL ,
  PRIMARY KEY (`idJugador`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;
 
 
-- -----------------------------------------------------
-- Table `futbol`.`equipo_jugadores`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `futbol`.`equipo_jugadores` ;
 
CREATE  TABLE IF NOT EXISTS `futbol`.`equipo_jugadores` (
  `idEquipo` INT(11) NOT NULL ,
  `idJugador` INT(11) NOT NULL ,
  PRIMARY KEY (`idEquipo`, `idJugador`) ,
  INDEX `fk_equipo_jugadores_equipos` (`idEquipo` ASC) ,
  INDEX `fk_equipo_jugadores_jugadores1` (`idJugador` ASC) ,
  CONSTRAINT `fk_equipo_jugadores_equipos`
    FOREIGN KEY (`idEquipo` )
    REFERENCES `futbol`.`equipos` (`idEquipo` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_equipo_jugadores_jugadores1`
    FOREIGN KEY (`idJugador` )
    REFERENCES `futbol`.`jugadores` (`idJugador` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

Bernardao · #3 (**permalink**) 26/07/2011, 19:51

Muchas gracias por aclararme mis errores de conceptos, me ha costado construirlo porque tenía marcado el unsigned data.
Voy a explicar lo que he entendido con mis propias palabras, menos técnicas que las tuyas, a ver si he entendido bien lo que he hecho.

Para crear una relación M:N entre dos relaciones, equipo-jugador en este caso, debo seguir los siguientes pasos:
1.- Creo una tabla Equipo_Jugadores en la que incluyo como claves primarias idjugador, idequipo.
2.- Defino dos claves foráneas, una para cada tabla y para cada id. idjugador con la tabla Jugadores y idequipo con la tabla Equipos.
3.- Ya está.

Muchas gracias de nuevo, ahora voy a ver si soy capaz de crear otra relación M:N que tengo en la misma bbdd.

Por cierto, la tabla Categoría no la he añadido pegándola aquí, porque no quería incluirla para dejarlo más claro, también tengo otras tablas con relación 1:N, pero esas tablas creo que las tengo bien puestas.

gnzsoloyo · #4 (**permalink**) 27/07/2011, 05:24

Cita:

la tabla Categoría no la he añadido pegándola aquí, porque no quería incluirla para dejarlo más claro,

El problema es que el script que pusiste creaba una FK que apuntaba a la tabla CATEGORIAS, pero como no estaba, fallaba a la hora de crear la tabla.
Cuando posteas un script así te conviene poner todas las tablas que estén relacionadas, aunque no sean parte de la solución o del problema, o bien eliminar de lo que posteas todas las FK cuya tabla no estés posteando.

Cita:

Para crear una relación M:N entre dos relaciones, equipo-jugador en este caso, debo seguir los siguientes pasos:
1.- Creo una tabla Equipo_Jugadores en la que incluyo como claves primarias idjugador, idequipo.
2.- Defino dos claves foráneas, una para cada tabla y para cada id. idjugador con la tabla Jugadores y idequipo con la tabla Equipos.
3.- Ya está.

Te voy a dar un camino más fácil.
Como por definición del modelo, toda relación N:N genera una tabla relacional, todas las CASE tools del mercado te crean una tabla física cuando vinculas dos tablas a través de un conector N:N. Esto significa que, en el caso de MySQL Workbench, lo que debes hacer es:
1) Crea la tabla JUGADOR con su PK.
2) Crea la tabla EQUIPO con su PK.
3) Selecciona el conector de relación N:N y haz un click en cada tabla y verás que instantáneamente el Workbech crea una tabla nueva donde las dos PK aparecen componiendo la PK de la nueva tabla.
A partir de allí puedes agregar todos los atributos que se necesiten en esa relación.

Como podrás apreciar, las cosas ya están solucionadas... simplemente debes aprender a aprovecharlas.

Tips finales:

1) Cuando uses columnas de tipo TINYINT, SMALLINT, MEDIUMINT, INT o BIGINT, y no vayas a usar valores negativos decláralas UNSIGNED.
Al usarlas sin el UNSIGNED, la mitad del rango de representación queda perdido porque está reservado a los números negativos. Así, por ejemplo, un TINYINT va de -128 a +127, mientras que el UNSIGNED va de 0 a 255.
La idea es que aproveches el rango, y que recuerdes que no existen los ID negativos. De hecho, el auto_increment no genera valores negativos.
2) Trata de no usar "ID" como nombre de identificador de las PK de las tablas. A la larga genera confusiones en las consultas con JOIN.
Lo que es habitual es usar un prefijo con el nombre de la tabla, para poder recordarlo más fácilmente (tiene otras ventajas), tal como jugador_id, torneo_id, etc. Si te fijas, Workbench crea automáticamente la primera columna con el "id" como prefijo más el nombre de la tabla, cuando creas las tablas en el diagrama directamente.
Es una práctica general de los desarrolladores.

Bernardao · #5 (**permalink**) 27/07/2011, 09:35

Cita:

Iniciado por gnzsoloyo

El problema es que el script que pusiste creaba una FK que apuntaba a la tabla CATEGORIAS, pero como no estaba, fallaba a la hora de crear la tabla.
Cuando posteas un script así te conviene poner todas las tablas que estén relacionadas, aunque no sean parte de la solución o del problema, o bien eliminar de lo que posteas todas las FK cuya tabla no estés posteando.

Si, me olvidé de borrarlo correctamente, sorry.

Cita:

Iniciado por gnzsoloyo

Te voy a dar un camino más fácil.
Como por definición del modelo, toda relación N:N genera una tabla relacional, todas las CASE tools del mercado te crean una tabla física cuando vinculas dos tablas a través de un conector N:N. Esto significa que, en el caso de MySQL Workbench, lo que debes hacer es:
1) Crea la tabla JUGADOR con su PK.
2) Crea la tabla EQUIPO con su PK.
3) Selecciona el conector de relación N:N y haz un click en cada tabla y verás que instantáneamente el Workbech crea una tabla nueva donde las dos PK aparecen componiendo la PK de la nueva tabla.
A partir de allí puedes agregar todos los atributos que se necesiten en esa relación.

Como podrás apreciar, las cosas ya están solucionadas... simplemente debes aprender a aprovecharlas.

Lo acabo de hacer como indicas y realmente es más rápido, pero no me arrepiento de haberlo aprendido a hacer a lo pardillo, pero ahora usaré el método que me recomiendas.

Cita:

Iniciado por gnzsoloyo

Tips finales:

1) Cuando uses columnas de tipo TINYINT, SMALLINT, MEDIUMINT, INT o BIGINT, y no vayas a usar valores negativos decláralas UNSIGNED.
Al usarlas sin el UNSIGNED, la mitad del rango de representación queda perdido porque está reservado a los números negativos. Así, por ejemplo, un TINYINT va de -128 a +127, mientras que el UNSIGNED va de 0 a 255.
La idea es que aproveches el rango, y que recuerdes que no existen los ID negativos. De hecho, el auto_increment no genera valores negativos.
2) Trata de no usar "ID" como nombre de identificador de las PK de las tablas. A la larga genera confusiones en las consultas con JOIN.
Lo que es habitual es usar un prefijo con el nombre de la tabla, para poder recordarlo más fácilmente (tiene otras ventajas), tal como jugador_id, torneo_id, etc. Si te fijas, Workbench crea automáticamente la primera columna con el "id" como prefijo más el nombre de la tabla, cuando creas las tablas en el diagrama directamente.
Es una práctica general de los desarrolladores.

Aquí me quedo con la duda, porque según entiendo yo, creo que yo he usado prefijos cuando he declarado los PK, también porque Workbench los incluye.
Sobre los unsigned, lo usaré, pero me dió problemas pq a alguna PK la declaré como unsigned y entonces no me permitía crear la realción N:N, pero quizás haciéndolo con tu método pueda declararlo.
Muchas gracias de nuevo crack!!!!

Bernardao · #6 (**permalink**) 27/07/2011, 09:51

Ya he entendido lo que me decías en el último punto, que no cree ID a secas y que le ponga siempre un prefijo o un sufijo xxxid o idxxx

gnzsoloyo · #7 (**permalink**) 27/07/2011, 15:07

Exacto. La idea es precisamente esa, que no se llame sólo "ID".

En cuanto al problema para crear las relaciones de Fk con las columnas UNSIGNED, en el 99% de las ocasiones es porque una es UNSIGNED y la otra no. El 1% restante, es porque usas campos de diferente rango (INT contra BIGINT, por ejemplo, o SMALLINT contra INT).
Las FK deben ser del mismo tipo, rango y/o collation que la PK de origen. Siempre.

Bernardao · #8 (**permalink**) 28/07/2011, 09:38

Cita:

Iniciado por gnzsoloyo

En cuanto al problema para crear las relaciones de Fk con las columnas UNSIGNED, en el 99% de las ocasiones es porque una es UNSIGNED y la otra no. El 1% restante, es porque usas campos de diferente rango (INT contra BIGINT, por ejemplo, o SMALLINT contra INT).
Las FK deben ser del mismo tipo, rango y/o collation que la PK de origen. Siempre.

Ok, entonces el otro día cuando tuve que quitar los UNSIGNED, mi problema debía ser que eran tipos diferentes y en lugar de quitar las UNSIGNED que tenía, debería haber puesto todos los id a UNSIGNED?

Y tengo otra duda sobre prácticas buenas.
Cuando diseño una tabla, la PK la pongo idcategoria por ejemplo, al relacionar esta tabla con otra tabla, en la segunda tabla donde es foránea la llamo "categoría" sin el id, es recomendable esta práctica?
Lo digo porque al hacerlo así me creo que será más fácil para mi identificar donde es PK y donde no. Pero quizás esté pasando por alto algunas cosas que me llevarán a problemas que de momento no soy capaz de planear.
Gracias teacher.