Integridad referencial con MySql Workbench

[y0mism0]

Buenas, ¿alguien me podría decir como puedo conseguir la integridad referencial usando el MySql Workbench? Supongamos que tenemos 3 tablas, usuarios, productos y productos_de_usuario, y en esta última pretendo meter los productos que tiene cada usuario, de forma que no deje meter productos que no existen ni usuarios que no existen, y que si quito un producto o un usuario se quite de la tabla productos_de_usuarios. He puesto como foreign key las claves primarias de las tablas usuarios y productos, que ahora me aparecen como tipo index, pero no funciona asi, ¿que es lo que tengo que hacer?

[gnzsoloyo]

Si no te deja crear la FK es muy probable (MUY probable) que haya una discrepancia de tipos de datos o collation que no se vea simple vista. A veces las diferencias son muy sutiles.
Infortunadamente Workbench no hace ninguna advertencia cuando creas el diagrama y se produce una incompatibilidad de ese tipo.
Cuando eso suceda, revisa el tipo de columnas que estás tratando de relacionar: deben ser del mismo rango si son numéricas, y de la misma collation si son de caracteres.

Para ejemplificarlo: Si un campo es INT y el otro INT UNSIGNED o BIGINT no tienen el mismo rango de datos, e incluso entre dos campos, uno TINYINT y otro TINYINT UNSIGNED, el rango es diferente.
En el caso de de los de caracteres (CHAR y VARCHAR), si una es utf8 y la otra latin1, no tienen la misma collation, por lo que no hay conicidencia en la página de caracters para las mismas letras.
Lo mismo pasa si se pretende vincular un DATE con un DATETIME.

En este caso no te permite crear las relaciones de clave foránea porque existe en uno de ellos un conjunto de datos posibles que jamás van a coincidir con el otro, y por tanto habrá INSERTs a una tabla perfectamente lícitos, que violarían el rango de la otra...

Supongoque se entiende la idea.

[y0mism0]

gracias, si, lo he entendido, pero todo esta bien, el caso es que me deja poner la clave ajena cuando creo la tabla, simplemente yendome a foreign key y poniendo el nombre de la foreign key, la tabla a la que hace referencia y el campo de la tabla que al que quiero aplicar esa clave ajena (aqui pongo lo mismo que en el nombre de la foreign key, la verdad es que no se para que sirve lo del nombre, si la foreign key debe de ser un campo de la tabla al que ya le di su nombre al crear los campos...) y no me da problemas, aparentemente la aplica, pero luego sin embargo puedo meter sin problemas datos que no existen en las otras tablas, puedo meter por ejemplo productos inexistentes o usuarios inexistentes, y no me da ningun error.

[y0mism0]

Buenas, subo este tema porque sigue dandome problemas lo mismo, ahora he vuelto a utilizar el Workbench y sigo sin poder la clave ajena. Ya he mirado todo eso de las discrepacias de datos pero esta todo en orden, no entiendo porque no sale en foreign keys las claves ajenas que defino, he probando tanto definiendolas ahí mismo como con una orden sql pero nada. Si defino una clave ajena en la pestaña foreign key luego me aparece solo en Indexes, y aunque cambie el valor en una tabla no me lo cambia en la otra.

Tengo una tabla authorities con una clave ajena username. Tengo otra tabla users con PK username. A pesar de cambiar el username en users no me lo cambia en authorities.

¿alguien le ha pasado lo mismo con MySql Workbench?

[neodani]

Iniciado por y0mism0

¿alguien le ha pasado lo mismo con MySql Workbench?

Siempre que me pasó algo así, lo solucioné como comenta gnzsoloyo, ambos campos de ambas tablas no eran exactamente iguales.

Saludos!

[y0mism0]

Iniciado por neodani

Siempre que me pasó algo así, lo solucioné como comenta gnzsoloyo, ambos campos de ambas tablas no eran exactamente iguales.

Saludos!

¿Pero te pasa que cuando tratas de poner la foreign key y le das a apply, te pone la siguiendo sentencia sql:

ALTER TABLE `proyecto`.`authorities`

ADD INDEX `nombreindice` (`username`) ;

y no te pone nada de FOREIGN KEY...? Igual si creo la tabla con una sentencia sql y pongo: constraint foreign key references, etc. Me crea un indice en la pestaña Indexes, con Index Name el nombre que le puse y en Type, INDEX.
Los campos son iguales, son varchar(45) y la collation igual.

No se lo que puede pasar

Saludos

[gnzsoloyo]

¿Estás implementando manualmente en SQL o lo estás haciendo en forma gráfica?
Por tu descripción lo segundo pareciera que no.
Para estar seguros de tu problema sería bueno que nos postearas la estructura de las tablas involucradas, y por estructura me estoy refiriendo al CREATE TABLE y no a una descripción manual de las mismas.
De esa forma podríamos ver exactamente qué es lo que estás haciendo mal, ya que el Workbench no falla en este punto.

[y0mism0]

Iniciado por gnzsoloyo

¿Estás implementando manualmente en SQL o lo estás haciendo en forma gráfica?
Por tu descripción lo segundo pareciera que no.
Para estar seguros de tu problema sería bueno que nos postearas la estructura de las tablas involucradas, y por estructura me estoy refiriendo al CREATE TABLE y no a una descripción manual de las mismas.
De esa forma podríamos ver exactamente qué es lo que estás haciendo mal, ya que el Workbench no falla en este punto.

Lo estoy haciendo de forma gráfica, lo que puse es la secuencia sql que me generó, me lo pone antes de darle a aplicar. He probado con un ejemplito muy simple, haciendolo de forma grafica y esto es lo que me genera:

Código:

CREATE  TABLE `MySchema`.`usuarios` (

  `idusuarios` INT NOT NULL ,

  PRIMARY KEY (`idusuarios`) );


CREATE  TABLE `MySchema`.`productos` (

  `idproductos` INT NOT NULL ,

  `usuarios` INT NOT NULL ,

  PRIMARY KEY (`idproductos`) ,

  INDEX `usuarios` (`usuarios` ASC) ,

  CONSTRAINT `usuarios`

    FOREIGN KEY (`usuarios` )

    REFERENCES `MySchema`.`usuarios` (`idusuarios` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION);

Ahora le doy a Alter Table y asi es como se queda mi foreign key 'usuarios', como si fuera un index, no se porque.



Saludos

[gnzsoloyo]

Si te fijas bien, la FK está perfectamente definida. Tu error es que su definición no se ve gráficamente en la tabla donde se genera, sino en la tabla a donde se toma.
Esto es: Selecciona la tabla usuarios y cuando vayas a la solapa de "Foreign Key", verás la indicación de que la PK se está usando como FK.
Un consejo que te puedo dar es que no uses la creación manual de las FK en solapa, sino que uses la creación por barra de herramientas, usando los conectores. De esa forma será el propio WorkBench el que te ponga el campo en la tabla donde esa PK va como FK.
Por otro lado, ten en cuenta que una FK genera un índice INDEX en todos los casos, que es el encargado de administrar la relación entre ambas tablas, y es INDEX porque una de sus cardinalidades es múltiple. Si la relación fuese 1:1, el índice sería UNIQUE.

[y0mism0]

Iniciado por gnzsoloyo

Si te fijas bien, la FK está perfectamente definida. Tu error es que su definición no se ve gráficamente en la tabla donde se genera, sino en la tabla a donde se toma.
Esto es: Selecciona la tabla usuarios y cuando vayas a la solapa de "Foreign Key", verás la indicación de que la PK se está usando como FK.
Un consejo que te puedo dar es que no uses la creación manual de las FK en solapa, sino que uses la creación por barra de herramientas, usando los conectores. De esa forma será el propio WorkBench el que te ponga el campo en la tabla donde esa PK va como FK.
Por otro lado, ten en cuenta que una FK genera un índice INDEX en todos los casos, que es el encargado de administrar la relación entre ambas tablas, y es INDEX porque una de sus cardinalidades es múltiple. Si la relación fuese 1:1, el índice sería UNIQUE.

Pues en la tabla usuarios tampoco me aparece nada en la solapa de foreign key...


Y tampoco me guarda la integridad, puedo meter en la tabla productos usuarios que no existan por ejemplo, no me da error ni nada parecido.

¿como se pone a foreign key utilizando conectores?

Por cierto, crei que el INDEX era para agilizar las busquedas de ciertos campos.

Gracias, un saludo

[gnzsoloyo]

¿como se pone a foreign key utilizando conectores?

¿No viste la barra de herramientas, al costado izquierdo? Bueno, los conectores están al final:

Y tampoco me guarda la integridad, puedo meter en la tabla productos usuarios que no existan por ejemplo, no me da error ni nada parecido.

En el ejemplo que pones en tu post anterior no se puede apreciar qué tipo de tablas estás usando: ¿MyISAM o InnoDB?

Por cierto, crei que el INDEX era para agilizar las busquedas de ciertos campos.

No solamente para eso. MyISAM crea un INDEX por cada relación con cardinalidad 1:N o N:N. Ese indice se usa precisamente para administrar esa relación. Es la forma en que se implementan.

[y0mism0]

Iniciado por gnzsoloyo

¿No viste la barra de herramientas, al costado izquierdo? Bueno, los conectores están al final:




En el ejemplo que pones en tu post anterior no se puede apreciar qué tipo de tablas estás usando: ¿MyISAM o InnoDB?

No solamente para eso. MyISAM crea un INDEX por cada relación con cardinalidad 1:N o N:N. Ese indice se usa precisamente para administrar esa relación. Es la forma en que se implementan.

Uso MyISAM.


A mi esa ventana de Diagram no me aparece, solo me sale algo asi:



Saludoss

[gnzsoloyo]

Vamos por partes:
1) No existen las FOREIGN KEY en MyISAM. No son parte del modelo de ese motor de tablas. Sólo existen en InnoDB, por lo que puedes definirlas cuantas veces quieras y jamás aparecerán.
2) ¿Qué versión de Workbench estás usando?
3) No me estás mostrando la solapa del diagrama (EER Diagram), sino la de Model Overview. Postea la del diagrama gráfico.

[y0mism0]

Iniciado por gnzsoloyo

Vamos por partes:
1) No existen las FOREIGN KEY en MyISAM. No son parte del modelo de ese motor de tablas. Sólo existen en InnoDB, por lo que puedes definirlas cuantas veces quieras y jamás aparecerán.
2) ¿Qué versión de Workbench estás usando?
3) No me estás mostrando la solapa del diagrama (EER Diagram), sino la de Model Overview. Postea la del diagrama gráfico.

Uso la version 5.2.31 CE

Verás, es que la captura que te puse no la hice yo, es de internet. es lo mismo que me sale a mi, pero no me fije en la pestaña de diagram, esa no me sale a mi por ningún sitio.

Por cierto, he cambiado las tablas a InnoDB y me sale el siguiente error al intentar poner la foreign key:

Código:

ERROR 1005: Can't create table 'proyecto.#sql-82c_2' (errno: 150)

SQL Statement:

ALTER TABLE `proyecto`.`authorities` 

  ADD CONSTRAINT `fk_username`

  FOREIGN KEY (`username` )

  REFERENCES `proyecto`.`users` (`username` )

  ON DELETE NO ACTION

  ON UPDATE NO ACTION

, ADD INDEX `fk_username` (`username` ASC)



ERROR: Error when running failback script. Details follow.



ERROR 1046: No database selected

SQL Statement:

CREATE TABLE `authorities` (

  `username` varchar(45) NOT NULL,

  `authority` varchar(50) NOT NULL,

  PRIMARY KEY (`authority`,`username`),

  UNIQUE KEY `ix_auth_username` (`username`,`authority`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

He probado a hacer una tabla de prueba y ponerla y me ha funcionado, ¿voy a tener que crear todas las tablas de nuevo?
Por alguna razon me crea las tablas por defecto en MyISAM, y ya le he puesto en preferences que use InnoDB pero nada.

Gracias

Saludos

[gnzsoloyo]

Verás, es que la captura que te puse no la hice yo, es de internet. es lo mismo que me sale a mi, pero no me fije en la pestaña de diagram, esa no me sale a mi por ningún sitio.

Cuando planteas un problema como este, no sirve usar genéricos, a menos que sean de manual. Si estamos hablando de tablas concretas tienes que poner TUS tablas, porque el problema no está en otra parte...
Usa servicios de imágenes para ponerlos de modo que luego podamos verlos y listo, como por ejemplo Imgur, o ImageShank

Respecto a los errores, si tienes tablas ya creadas y con datos, hay que asegurarse que los datos cumplan con la restricción de FK. Con sólo uno que no lo cumpla, MySQL no puede crear la FK. Mi consejo es que si ese es el caso, vacía la base completamente, siempre que puedas. Trabajar con bases vacías es siempre mas fácil.
Si la base no tiene datos, hay que verificar que se cumpla que los tipos de campo relacionados sean de la misma COLLATION y/o rango, que si en la tabla referida la PK es compuesta, se respeten campos y orden de la clave; que no se esté apuntando a campos no clave, a menos que sean parte de indices UNIQUE, etc.
Es decir, si tienes errores en el diagrama, no se generarán las relaciones, o bien no se completará la construcciçon de la base.

Finalmente, el último error hace mención de "base no seleccionada". Eso puede ser un error de generación del script, pero también que no se ha indicado que genere la base en sí, por lo que no incluye el CREATE DATABASE donde debe. Verifica todo.

Respecto la diagrama, ¿no has abierto o indicado que se agregue un diagrama?
¿Nada?

[y0mism0]

Iniciado por gnzsoloyo

Cuando planteas un problema como este, no sirve usar genéricos, a menos que sean de manual. Si estamos hablando de tablas concretas tienes que poner TUS tablas, porque el problema no está en otra parte...
Usa servicios de imágenes para ponerlos de modo que luego podamos verlos y listo, como por ejemplo Imgur, o ImageShank

Respecto a los errores, si tienes tablas ya creadas y con datos, hay que asegurarse que los datos cumplan con la restricción de FK. Con sólo uno que no lo cumpla, MySQL no puede crear la FK. Mi consejo es que si ese es el caso, vacía la base completamente, siempre que puedas. Trabajar con bases vacías es siempre mas fácil.
Si la base no tiene datos, hay que verificar que se cumpla que los tipos de campo relacionados sean de la misma COLLATION y/o rango, que si en la tabla referida la PK es compuesta, se respeten campos y orden de la clave; que no se esté apuntando a campos no clave, a menos que sean parte de indices UNIQUE, etc.
Es decir, si tienes errores en el diagrama, no se generarán las relaciones, o bien no se completará la construcciçon de la base.

Finalmente, el último error hace mención de "base no seleccionada". Eso puede ser un error de generación del script, pero también que no se ha indicado que genere la base en sí, por lo que no incluye el CREATE DATABASE donde debe. Verifica todo.

Respecto la diagrama, ¿no has abierto o indicado que se agregue un diagrama?
¿Nada?

Al final cree las tablas de nuevo, no se porque no me cogia la base de datos.

¿pero como abro un diagrama?
Por cierto, ¿ese diagrama se puede exportar a un jpg por ejemplo? ¿son diagramas UML o ERD? Estoy haciendo un proyecto y necesito diagramas UML o ERD y no se como hacerlos.


Saludos

[cristian_cena]

Me pasó lo mismo, y la razón por la cual me exportaba el código sql sin las foreign keys más allá de que el tipo de dato era el mismo para todas las claves residía en que le estaba errando en las opciones de exportación.

En el Modelo ER, apretamos ctrl+shift(May)+G
Ya en las opciones de exportación presionaba los siguientes checks:

• generate DROP Statements Before Each CREATE Statement
• Skip Creation of FOREIGN KEIS

Deseleccioné "Skip Creation of FOREIGN KEIS" y exportó perfectamente el código .

Puede pasar asi que lo comparto para quien sea tan despistado como yo jaja.

Saludos.