Pedir presupuesto

Tutorial Mysql – Relaciones entre tablas y su integridad referencial

Ejemplo de Tablas relacionales en MySql

Muchos habreis tenido problemas al crear tablas relacionadas con MySql, pero de verdad que es bastante sencillo, solo hay que tener en cuenta una serie de premisas:

  • Las tablas que se van a relacionar tienen que ser tipo InnoDb(InnoDB es el primer tipo de tabla que permite definir estricciones de claves foráneas para garantizar la integridad de los datos).
  • Usar sintaxis FOREIGN KEY (campo_fk) REFERENCES nombre_tabla(nombre_campo)
  • Crear un gndice en el campo que ha sido declarado claveforánea

Es necesario el uso de índices para que la verificación de las claves foráneas sea más rápida. Vamos a ver un ejemplo de una definición de 2 tablas relacionadas. Clientes y privilegios:

[mysql]
CREATE TABLE clientes
(
id_cliente INT NOT NULL AUTO_INCREMENT,
nombre VARCHAR(30),
PRIMARY KEY (id_cliente)
) TYPE = INNODB;

CREATE TABLE privilegios
(
id_privilegio INT NOT NULL AUTO_INCREMENT,
id_cliente INT NOT NULL,
privilegio INT(2),
PRIMARY KEY(id_privilegio),
INDEX (id_cliente),
FOREIGN KEY (id_cliente) REFERENCES clientes(id_cliente)
) TYPE = INNODB;
[/mysql]

De esta manera estamos relacionando la tabla clientes y privilegios por el campo id_cliente.

La clave foránea (FOREIGN KEY) y la Referenciada tienen que tener tipos de datos similares para que puedan ser comparadas sin la necesidad de hacer una conversión de tipos.

Ahora ya tenemos las tablas relacionadas y seguro que os haceis la siguiente pregunta:

«¿Cómo inserto registros en las tablas que tengan claves foráneas (FOREIGN KEYS)?»

Bueno, esta pregunta realmente nos la podemos hacer para cuando se inserta un registro, se borra o se actualiza, ya que en las tres lo tendremos que tener en cuenta.

Ahora vamos a insertar algún registro para verificar que todo está correcto:

[mysql] INSERT INTO clientes VALUES (1, ‘Pedro Picapiedras’);
INSERT INTO clientes VALUES (2, ‘Pablo Marmol’);
INSERT INTO clientes VALUES (3, ‘Ana Botella’);[/mysql]

[mysql] INSERT INTO privilegios VALUES (1,1,10);
INSERT INTO privilegios VALUES (2,3,05);
INSERT INTO privilegios VALUES (3,2,01);[/mysql]

Nota: No me pregunteis porque he puesto Ana Botella dentro de esta categoría de clientes :D.

Hasta ahora ha ido todo bien, pero ¿que pasaría si intentamos insertar un registro en la tabla «privilegios» en el que nos refiramos a un cliente que no exista en la tabla clientes?, vamos a probarlo:

[mysql] INSERT INTO privilegios VALUES (4,5,10);[/mysql]

Pues sí nos tiene que devolver un error tal como este:

«ERROR 1216: Cannot add or update a child row: a foreign key constraint fails»

La razón es bastante evidente, estamos intentando insertar un registro en la tabla privilegios sobre un cliente «id_cliente=5», y hasta el momento no existe.

Ahora vamos a intentar eliminar un registro de la tabla clientes:

[mysql] DELETE FROM cliente WHERE id_cliente=3;[/mysql]

Debido a que tenemos la restricción de la clave foránea no permitirá eliminar el registro puesto que se hace referencia a él en la tabla «privilegios», por lo tanto Mysql nos devolvería el siguiente error:

«ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails»

Para eliminar este tipo de registros con claves foráneas existen lo que se llama eliminación en CASCADA, en la que todos los registros relacionados se eliminan según las relaciones de claves foráneas.

Para llevar a cabo una eliminación de todos los registros relacionados por las claves foráneas en distintas tablas se usa la funcion «ON DELETE CASCADE».

Si se especifica ON DELETE CASCADE, y una fila en la tabla padre es eliminada, entonces se eliminarán las filas de la tabla hijo cuya clave foránea sea igual al valor de la clave referenciada en la tabla padre. Esta acción siempre ha estado disponible en MySQL. Fuente: Mysql-hispano.org

Veamos un ejemplo de eliminación en cascada:

[mysql]ALTER TABLE privilegios ADD FOREIGN KEY(id_cliente)
REFERENCES cliente(id_cliente) ON DELETE CASCADE;[/mysql]

Con esta sentencia, ya tendremos dispuesta la tabla para que se pueda eliminar en cascada. Por lo tanto si queremos eliminar ahora al cliente «Ana Botella», tan sólo tendriamos que ejecutar lo sisguiente:

[mysql]DELETE FROM cliente WHERE id_cliente=3;[/mysql]

Si haceis una consulta ahora en las dos bases de datos, «clientes» y «privilegios», vereis como se ha eliminado «Ana Botella» de las dos tablas.

Al igual que la eliminación también podemos actuar en cascada con las actualizaciones con la sentencia «ON UPDATE CASCADE».

Esto es todo, espero ayudaros a entender un poco mejor la integridad referencial en Mysql y la relación entre tablas.

Vía: MySql-Hispano – Integridad referencial en MySQL

¿Te ha gustado?, Comparte!!

32 comentarios

  1. muy buen tuto. muy simple y claro.
    lo q mas me gusto fue la image de arriba que tienes con las tablas.
    me gustaria saber que programas usas para poder crearlas y ahorrate el trabajo de luego pasar el script???

    desde ya gracias.

    saludos

  2. Tienes varias opciones, pero claro todas las opciones sirven para generar el diagrama después de crear la base de datos:

    Db Designer
    WorkBench
    SQL Server 2005 Management Studio (esta opcion es mas cara :D).

    En todos estos puedes hacer un DER (Diagrama de Entidad Relacion) y al revés, de un modelo de datos hacerte una tabla.

    espero te sirva …

    Un saludo.

  3. al momento de querer eliminar es cierto no elimina el registro y es porque no existe la tabla que estas colocando:

    DELETE FROM cliente WHERE id_cliente=3; utilizas la tabla «cliente» cuando deberia ser el nombre de la tabla correcta «clientes» como tu lo estas llamando en un inicio al corregir esaconsulta NO ME ELIMINA EL REGISTRO

    DELETE FROM clientes WHERE id_cliente=3

  4. Buenas admin…necesito que me ayudes…no tengo nada de experiencia en mysql…tengo 2 tablas..y lo que kiero es lo siguiente: que una tabla de «Curso» con la clave «Codigo» tenga varias personas..porque cada vez que registro una persona y kiero registrrar otra en el mismo curso o «codigo» no puedo xq el codigo del curso es el mismo…y sabes que las claves primarias no se duplican…xfa compadre te agradezco la ayuda que me puedas aportar…espero respuesta…

  5. Hola Carlos,

    Me sería de gran ayuda si dijeras mas detalladamente como tienes hechas las tablas con sus campos. Es decir, el tipico CREATE TABLE CURSOS ……

    Si estas haciendolo con MySQL, puedes exportar la base de datos a un txt y asi lo podras pegar aqui.

    Saludos.

  6. MUY BUEN TUTORIAL ERA LO QUE ESTABA BUSCANDO POR PERSONAS COMO ESTA ES QUE HE PODIDO COMPLETAR ALGUNOS PROYECTOS FELICITACIONES OJALA CONTINUEN AYUDANDO A TODA LA COMUIDAD INFORMATICA…..
    GRACIAS….

  7. hola que tal, yo tengo una duda estoy haciedno una pagian en donde se hace un registro de personas, son 4 tablas y 4 pantallas cada tabla va con una pantalla, pero en realidad no se como relacionarlas para al momneot de hacer una busqueda esten relacionadas las tablas y me jale los datos de un usuario y sus datos que le corresponden en cada tabla, yo hago esto pero me marca error:
    para la tabla password y pantalla donde crea su user y pass
    $que = «INSERT INTO password (USER, PASS FROM password INNER JOIN perfil on password.ID=perfil.ID INNER JOIN escolaridad on password.ID=escolaridad.ID INNER JOIN laboral on password.ID=laboral.ID) «;
    tabal perfil don ingresa datos personales:
    $que = «INSERT INTO perfil (NOMBRE_S, APELLIDO_PATERNO, APELLIDO_MATERNO, NUMERO_CONTROL, CARRERA_EGRESO, ESPECIALIDAD, SEXO, CALLE, NUMERO, COLONIA, CODIGO_POSTAL, CD, MUNICIPIO, ESTADO, TELEFONO_LADA, E_MAIL, CELULAR, SEMESTRE_EGRESO, ANO_EGRESO, TITULADO FROM password INNER JOIN perfil on password.ID=perfil.ID INNER JOIN escolaridad on password.ID=escolaridad.ID INNER JOIN laboral on password.ID=laboral.ID) «; y y las otras tablas escolaridad y laboral, casi lo mismo pero ya con una sale las otras espero me puedas ayudar

  8. Hola,

    Trato de hacer esta tabla (orders) en la que enlazo dos empresas diferentes (deste una tabla «company») y 4 contactos diferentes (cogidos de tabla externa «contacts»)… Qué pueso estar haciendo mal con las references?

    Gracias!

    CREATE TABLE IF NOT EXISTS `db`.`orders` (
    `id_order` INT(11) NOT NULL AUTO_INCREMENT ,
    `id_company` INT(11) NOT NULL ,
    `id_related_company` INT(11) NULL DEFAULT NULL ,
    `id_contact_1` INT(11) NOT NULL ,
    `id_contact_2` INT(11) NULL DEFAULT NULL ,
    `id_contact_3` INT(11) NOT NULL ,
    `id_contact_4` INT(11) NOT NULL ,
    PRIMARY KEY (`id_order`) ,
    CONSTRAINT `orders_ibfk_1`
    FOREIGN KEY (`id_company` , `id_related_company` )
    REFERENCES `db`.`companies` (`id_company` , `id_company` ),
    CONSTRAINT `ordenes_ibfk_2`
    FOREIGN KEY (`id_contact_1` , `id_contact_2` , `id_contact_3` , `id_contact_4` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB
    AUTO_INCREMENT = 1
    DEFAULT CHARACTER SET = utf8;

    CREATE INDEX `id_related_company` ON `db`.`orders` (`id_related_company` ASC) ;

    CREATE INDEX `id_company` ON `db`.`orders` (`id_company` ASC) ;

    CREATE INDEX `id_contact_1` ON `db`.`orders` (`id_contact_1` ASC) ;

    CREATE INDEX `id_contact_2` ON `db`.`orders` (`id_contact_2` ASC) ;

    CREATE INDEX `id_contact_3` ON `db`.`orders` (`id_contact_3` ASC) ;

    CREATE INDEX `id_contact_4` ON `db`.`orders` (`id_contact_4` ASC) ;

  9. pues yo intento hacer una relacion entre mis tablas en mi web.. pero resulta que TYPE=INNODB no esta…. alguien puede decirme porq a mi no me aparece… ni en mi localhost ni en mi servidor web aparece ese type.. por favor alquien conteste es urgente a mi correo piritudigital@hotmail.com

  10. hola es buneo saber e enzeñar a los que son novatos en esto de msql (Software libre.. para seres humanos)..tu debes de ser uno de los mejores en programcion programs libres y tambine quiero ser libre como tu ……
    bueno me anuncio es : tengo un base de datos llamdo ALFA dentro de este estan los 5 tablas:llamados usuario,clinete,productos,pedidos y detalles de pedidos lo que osea son 5 tablas pero de las cuales yo creo que la tabla usuario no va relacionado con ninguno veradad?? quiero es?…. relacionar las 4 tablas anteriores me pregunta es como colocar las claves primarias y foraneas en tablas ya relacionadas mi relacion es (cliente y pedidos) and (producto y detalle de pedido) and (pedido con detalles de pedido) lo que quiero sabe res como es sintaxis o sentencia que debo poner en tablas deracinadas : no me gusta crear dentro de mysql pero si lo hago en un Editro de texto…..por vafor soy novatos en esto….mi corre es: bradysam»arroba»hotmail.com

  11. CREATE TABLE usuarios
    (
    id int(5) not null auto_increment,
    fecha int(10) not null,
    user varchar(50)not null,
    pass varchar(32)not null,
    mail varchar(50)not null,
    primary key(id), // es en estos mi problema
    )

    ——————
    CREATE TABLE clinete
    (
    id_cli int (5) not null auto_increment,
    nombre_cli varchar (50) not null,
    apellido_cli varchar(50) not null,
    direccion_cli varchar(50) not null,
    telefono_cli varchar(16) not null,
    email_cli varchar (50) not null
    )

    //las relaciones esta entre clinete y pedidos

    CREATE TABLE pedidos
    (
    id pedidos int (5) not null auto_increment,
    tipo_pe varchar(50) not null,
    fecha_pe varchar(10) not null,
    direcccion_pe varchar(50) not null,
    detalle_pe varchar(50) not null,
    ciudad_pe varchar(50) not null,
    foto_pe var varcahr(50) not null,// mi promb es en esto de foto tambine exactamente como es tipos de vareables o sentaxis.
    id_clinet int (5) es exactamente mi problema como relaciono ??»’ es asi no mas o falta algo mas
    )
    ————————
    CREATE TABLE productos
    (
    id_pro int (5) not null auto_increment,
    nombre_pro varchar(50) not null,
    detalle_pro varchar(50) not null,
    foto_pro varchar(50) // lo misma pregunta anterior por fa ayuda o otra tabla solo para foto????
    cantidad_pro varchar(50),
    )
    ///esto va relacionado con esta tablas (detalle del pedido)
    CREATE TABLE dpedido
    (
    id_dpe int(5) not null auto_increment,
    id_pro int (5) not null,
    id_pro int(5) not null,
    descripción_dpe varchar(50)not null,
    detalle_dpe varchar(50)not null,
    observacion_dpe varchar(50) not null,
    )
    //estos son mi tablas por favor.
    es asi como van los claves o o como por favor gracias …….
    att: brady………

  12. En muy pocos sitios se hallan buenas explicaciones….éste es uno de los pocos.
    Bueno, despues del elogio prosigo con la explicación de algo que no le hallo solución todavía.
    Estoy tratando de registrar los datos de una guía de remisión en un BD. Tengo:
    1. numero de guía de remisión – grID – varchar
    2. de (nombre de la persona que hace el envío) – grDE -varchar
    3. para (nombre de la persona que recibe la mercadería) – grPARA – varchar
    4. cantidad (ej: del producto «x») – grCANT – varchar
    5. descripción (ej: del producto «x») – grDESC – text
    6. precio (ej: de la cantidad del producto «x») – grPRECIO – int
    7. total (ej: suma total de todos precios) – grTOTAL – int

    Entonces: lo que deseo es tener el «número de guía de remisión» como llave primaria para luego extraer toda la información de cada guía…..pero cómo?? eso no lo sé.
    A mí se me ocurrió algo así:
    creé 7 campos
    grID – grDE – grPARA – grCANT – grDESC – grPRECIO – grTOTAL

    pero solamente habrá una línea de registro para todos los campos menos para grCANT y grDESC porque estos últimos 2 se repetirán más de una vez por la cantidad de artículos.
    Me dejo entender?
    Bueno, apreciaré mucho la ayuda de cualquier buenalma que esté rondando los predios de este forum.

  13. Gracias «manguito», se agradecen mucho estos comentarios.

    Hasta donde puedo entender, lo que haría es lo siguiente:

    – Una tabla que se llame REMISIONES: Con los siguientes campos (grId, grDe, grPara, grIdProducto, grCant, grTotal)
    – Otra tabla que se llame PRODUCTOS: Con los siguientes campos(grIdProducto, grDesc, grPrecio)

    Fijate que el campo grIdProducto está en las dos tablas, este es el que actua como relacional, y sería el identificador (clave primaria) de la tabla PRODUCTOS.

    Espero que te haya servido.

  14. Estimados
    Tengo
    – tabla llamada comercio (datos del comercio)

    tengo tres tablas
    Rubros
    Subrubros
    Sub_subrubros

    Como relaciono estás 3 tablas con la de comercios
    Gracias de antemano por la contestación

    Una vez relacionada. No puedo recordarme el comercio por el id del comercio
    Como hago para insertar con el nombre del comercio insertar en la tabla el id correspondiente

    Saludos y gracias de antemano

    Amadeo

    1. pues yo lo que haria es relacionarlas cpn el id de cada una en la tabla
      comercio. Es decir:

      comercio
      –> id_comercio
      id_rubros
      id_subrubros
      id_subsubrubros
      otros campos

      rubros
      –>. id_rubros
      id_subrubros
      otros campos

      subrubros
      –>. id_subrubros
      otros campos

      Cada uno de estos campps va relacionado con su correspondiente en las otraa tablas.

      No se si te resuelve esto la duda

      Saludos

  15. Muy buen tutorial. Que pasaria con los registros de ‘clientes’ en el caso de que se desee eliminar un registro de la tabla ‘privilegios’ ?

    Gracias

  16. Gracias me sirvió de bastante, he estado aprendiendo mysql y me ha costado un poco entender lo de las relaciones, lo explicas de una manera bastante clara, sé que debo practicar más pero es un gran comienzo.

  17. Muy buen tutorial, te felicito muy bien explicado.

    ¿Tienes mas sobre MySql?, si es asi me gustaría echarles un vistazo.

    Buen trabajo!!!!!!!

  18. No sabes cuanto me «cabecie» porque queria hacer una referencia a una tabla con auto_increment con una que no lo tenia y era que habia asignado el valor «UNSIGNED» luego del int….. bueno , gracias ati sali del paso. MUCHAS GRACIAS

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

¡Pide tu presupuesto!

Completa los campos para poder enviarte un presupuesto

Todos y tu presupuesto son a medida y totalmente personalizados, entendemos que dos proyectos online no pueden ser iguales y que cada cliente tiene unos objetivos y necesidades muy diferentes, es por eso que para elaborar un presupuesto, necesitamos saber de ti, tu proyecto y objetivos.

Los campos marcados con (*) son obligatorios.

La comunicación enviada quedará incorporada a un fichero del que es responsable COLORDEU. Esta comunicación se utilizará exclusivamente para tratar sus datos para atender su solicitud, siempre de acuerdo al Reglamento (UE) 2016/679 (RGPD), la Ley Orgánica 15/1999 (LOPD) y el Real Decreto 1720/2007 de desarrollo de la LOPD), sobre protección de datos. Sus datos no se comunicarán a terceros, excepto por obligación legal, y se mantendrán mientras no solicite su cancelación. En cualquier momento usted puede ejercer los derechos de acceso, rectificación, portabilidad y oposición, o si procede, a la limitación y/o cancelación del tratamiento, comunicándolo por escrito, indicando sus datos personales mediante un email a hola@colordeu.es