Creación de tabla relacional con llaves foráneas en MySQL

Clase 12 de 24 • Curso de Fundamentos de Bases de Datos

Resumen

Crear correctamente las tablas y gestionar sus relaciones en MySQL es clave para mantener la integridad y eficiencia de la base de datos. Partiendo de las tablas ya creadas (clients, products y builds), exploraremos cómo establecer conexiones significativas utilizando llaves foráneas (foreign keys) y gestionarlas tanto con relaciones "duras" (a nivel base de datos) como "suaves" (en lógica de aplicación).

¿Cómo crear correctamente una tabla nueva relacionada con otras tablas?

Para conectar información entre tablas, crearemos una tabla llamada bill products, que almacenará productos específicos relacionados con una factura (bill). Es esencial que los nombres de tablas sean coherentes y claros para cualquier colaborador.

Ejemplo de creación de tabla:

CREATE TABLE IF NOT EXISTS bill_products (
  bill_product_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  bill_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  created_at DATETIME,
  updated_at DATETIME
);

¿Cuándo y cómo usar llaves foráneas tipo "cascade"?

Las llaves foráneas con ON DELETE CASCADE aseguran que, al eliminar un registro en la tabla padre, todos los registros relacionados se eliminen automáticamente. Es una función potente, pero conlleva riesgos si no se maneja con precaución.

Ejemplo específico:

FOREIGN KEY (bill_id) REFERENCES bills(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(product_id) ON UPDATE CASCADE ON DELETE CASCADE

Este método facilita mantener la integridad de datos, pero puede causar pérdidas no deseadas si se elimina información inadvertidamente.

¿Qué ventajas tiene usar relaciones "suaves" sobre relaciones "duras"?

Las relaciones "suaves" permiten mayor flexibilidad ya que la lógica de validación está en la aplicación, no en la base de datos directamente.

Sus ventajas principales son:

Mayor flexibilidad para gestionar cambios.
Menor rigidez en la estructura.
Facilita cambios rápidos sin modificar constantemente la estructura en la base.

Aunque presenta riesgos como insertar datos huérfanos, bien manejado puede ayudar en entornos de desarrollo ágil o proyectos en crecimiento.

Este panorama práctico sobre creación de tablas, relaciones y llaves foráneas busca informarte sobre opciones clave según las necesidades específicas de tu proyecto. ¿Prefieres utilizar relaciones "suaves" enfocadas en lógica de aplicación o eres partidario de relaciones "duras" en la base de datos? Déjanos tu opinión en los comentarios.

Enrique Alexis Lopez Araujo

student•

Un antipatrón que se mencionó en clase es utilizar relaciones duras (foreign keys) sin considerar el impacto en el diseño y mantenimiento de la base de datos. Esto puede llevar a problemas graves, como la pérdida de datos al eliminar registros en cascada sin un control adecuado. Es preferible implementar la lógica de relaciones en la capa de aplicación, lo que ofrece más flexibilidad y facilidad de mantenimiento. Evitar este enfoque ayudará a mantener la integridad de los datos y facilitará futuras modificaciones.

Joshua Edwin Ginez Eligio

student•

pasa mas contexto que solo me espanto jajaja :c

Gonzalo Peñaranda

student•

Es un buen enfoque.

Aunque, también, en el caso de que realmente no se requiera eliminar algo, simplemente especificar un cambio de estado, como 'inactivo', para evitar pérdida de información clave.

Carlos Villavicencio

student•

InnoDB y MyISAM son motores de BD. MyISAM es mas antiguo y no muy bueno para nuestros fines.

Jorge Ivan Meza

student•

Motores de almacenamiento de tablas*

Enrique Alexis Lopez Araujo

student•

Se presentaron los siguientes comandos relacionados con la creación de tablas en SQL:

CREATE TABLE: Se utiliza para crear una nueva tabla.
FOREIGN KEY: Define una relación entre dos tablas, asegurando la integridad referencial. En este caso, se usó para hacer referencia a las tablas bills y products.
ON UPDATE CASCADE / ON DELETE CASCADE: Permiten que los cambios en la tabla referenciada se propaguen automáticamente a la tabla que contiene la clave foránea.

En resumen, estos comandos permiten crear relaciones entre tablas y mantener la integridad de los datos en la base de datos.

Enrique Alexis Lopez Araujo

student•

Las foreign keys son cruciales en bases de datos relacionales porque garantizan la integridad referencial entre tablas. Esto significa que cualquier valor en una columna de foreign key debe coincidir con un valor en la tabla referenciada, evitando datos huérfanos y asegurando relaciones consistentes.

Ejemplo de buenas prácticas:

Al crear una tabla de "pedidos", una foreign key puede referirse a "clientes". Esto asegura que cada pedido esté asociado a un cliente existente, protegiendo la integridad de los datos.

Implementar foreign keys mejora la calidad y la estructura de tus datos, facilitando el mantenimiento y las consultas eficientes.

Héctor Galindo

student•

Gemini: Excelente que estés aprendiendo SQL y bases de datos. Con las tablas que has creado, podemos evaluar tu comprensión de varios conceptos fundamentales. Prepárate para responder las siguientes preguntas de la manera más precisa y concisa posible.

Pregunta 1:

Describe detalladamente la función y el propósito de la cláusula PRIMARY KEY en la tabla clients. ¿Qué implicaciones tiene que la columna client_id sea la clave primaria y además tenga el atributo AUTOINCREMENT?

Pregunta 2:

En la tabla products, la columna slug tiene la restricción UNIQUE. Explica por qué podría ser útil tener una columna slug única además del product_id como identificador principal. Proporciona un ejemplo concreto de su posible uso.

Pregunta 3:

La tabla bills contiene una clave foránea (FOREIGN KEY) que referencia a la tabla clients.

a) ¿Cuál es el nombre de la columna que actúa como clave foránea y a qué columna de la tabla clients se refiere?
b) Explica qué significa la cláusula ON DELETE CASCADE en esta relación. ¿Qué sucedería si se eliminara un registro de la tabla clients?
c) Explica qué significa la cláusula ON UPDATE CASCADE en esta relación. ¿Qué sucedería si se modificara el valor de la clave primaria en un registro de la tabla clients?

Pregunta 4:

La tabla bill_products establece una relación de muchos a muchos entre las tablas bills y products.

a) ¿Por qué es necesaria una tabla intermedia como bill_products para representar esta relación?
b) Identifica las claves foráneas en la tabla bill_products y a qué tablas y columnas referencian.
c) Describe cómo podrías obtener una lista de todos los productos incluidos en una factura específica (por ejemplo, la factura con bill_id = 5). Escribe la estructura general de la consulta SQL que utilizarías (sin necesidad de datos específicos).

Pregunta 5:

Considerando la columna status en la tabla bills, que es de tipo ENUM('open', 'paid', 'lost') NOT NULL DEFAULT 'open':

a) ¿Qué significa el tipo de dato ENUM? ¿Cuáles son los valores permitidos para esta columna?
b) ¿Qué implica la restricción NOT NULL en esta columna?
c) ¿Cuál es el valor por defecto que se asignará a la columna status cuando se inserte un nuevo registro en la tabla bills y no se especifique un valor para esta columna?

Responde estas preguntas con precisión y demuestra tu comprensión de los conceptos de SQL involucrados en la creación de estas tablas. ¡Adelante!

Héctor Galindo

student•

Mi respuesta:

---- 1. El propósito de la cláusula PRIMARY KEY es marcar esa columna como el campo para un identificador único de cada registro en la tabla. La columna "client_id" será la columna donde se muestre y asigne un identificador único autoincremental a cada nuevo registro que se realice en la tabla clients. Esta columna no puede tener un valor nulo, por lo que le asignamos el atributo "AUTOINCREMENT", de esta manera no tendremos que pasarle un ID manualmente cada que creemos un registro.

2. Esto depende de la lógica de negocio del problema a resolver, en este caso la columna "slug" almacena un identificador único para cada producto, con el fin de encontrar este producto facilmente como un parametro de consulta en una URL. Cada nuevo registro debe tener un "slug" único, es por eso que hacemos uso de la restricción "UNIQUE".

3. a. El nombre de la columna que actúa como llave foranea dentro de la tabla "bills" es "client_id", y al momento de declarar la llave foranea se hace una referencia a la columna "client_id" de la tabla "clients".

b. La cláusula "ON DELETE CASCADE" especifica cómo debería manejar la base de datos el intento de eliminar un registro de la tabla clients cuando existe una relación fuerte por medio de una llave foránea a otra tabla. Con esta cláusula la base de datos eliminará el registro que coincida con la llave foránea en la tabla hija. Lo que sucedería si eliminara un registro en la tabla clients, sería que también se eliminará automaticamente el registro en la tabla "bills"

c. Basicamente, es la misma lógica que con la anterior cláusula, pero esta se especifica para decirle a la base de datos cómo manejar un cambio en la primary key de la tabla padre. En este caso, se actualizará en cascada a la llave foranea de la tabla hija.

4. a. Es necesaria para normalizar los datos de los esquemas, pues estamos separando lógica que puede representarse en otra tabla. Además, estamos creando relaciones con otras tablas y haciendo uso de claves foráneas para mantener la integridad referencial de los datos.

b. Las claves foráneas en la tabla "bill_products" son "bill_id" y "product_id", estas referencian a las tablas "bill" y "products" con los mismo nombres de columnas. Esto nos ayuda a no duplicar datos y a crear relaciones.

c. SELECT * FROM bill_products WHERE bill_id = 5

5. a. El tipo de dato "ENUM" es usado para ofrecer una lista de opciones predefinidas para el campo, de esta forma podemos asegurarnos de que el valor que se ingresará sea específicamente el requerido por el sistema.

b. Significa que su valor no puede ser nulo, es decir, no puede no tener un valor almacenado.

c. El valor por defecto será "open", este también pertenece al enum.

Natalia Gallego Heras

student•

Adoro este curso y sé que hace un año no lo habría sentido así: frases que el profesor dice rápido porque se da cuenta de que no está siendo preciso pero que no hay necesidad de detenerse en ello no me complican, no me atoro pensando que me perdí algo importante. Sé lo que quiere decir y la importancia que tiene. Me alegro de que exista Platzi, he aprendido mucho 💫

Michele Roldan

student•

Hola, éste es mi código: CREATE TABLE clients (

client_ID integer unsigned primary key auto_increment,

name varchar(100) not null,

email varchar(100) not null unique,

phone_number varchar(15) not null,

created_at timestamp not null default current_timestamp,

modified_at timestamp not null default current_timestamp on update current_timestamp

);

CREATE TABLE IF NOT EXISTS products(

product_id integer unsigned primary key auto_increment,

name varchar(100) not null,

slug varchar (200) not null unique,

description text,

created_at timestamp not null default current_timestamp,

modified_at timestamp not null default current_timestamp on update current_timestamp

);

CREATE TABLE IF NOT EXISTS bills(

bill_id integer unsigned primary key auto_increment,

client_id integer not null,

total float,

status enum('open', 'paid', 'lost') not null default 'open',

foreign key (client_id) references clients(client_ID),

created_at timestamp not null default current_timestamp,

modified_at timestamp not null default current_timestamp on update current_timestamp

);

CREATE TABLE IF NOT EXISTS bill_products(

bill_product_id integer unsigned primary key auto_increment,

bill_id integer unsigned not null,

product_id integer unsigned not null,

quantity integer not null default 1,

total float,

created_at timestamp not null default current_timestamp,

modified_at timestamp not null default current_timestamp on update current_timestamp,

foreign key (bill_id) references bills(bill_ID)

on update cascade

on delete cascade,

foreign key (product_id) references products(product_ID)

on update cascade

on delete cascade

);

Pero al correr la creación de la tabla bills tengo este error:

Error Code: 3780. Referencing column 'client_id' and referenced column 'client_ID' in foreign key constraint 'bills_ibfk_1' are incompatible.0.000 sec

Alguien podría decirme qué estoy haciendo mal?

Alan Alejandro Villegas Espinoza

student•

Igual me paso el mismo error, la solucion es sencilla:

Asegúrate de que el tipo de datos de client_id en ambas tablas (client y bills) sea exactamente el mismo, incluyendo UNSIGNED.

Cambia esta línea en la tabla bills:

CREATE TABLE IF NOT EXISTS bills(

bill_id integer unsigned primary key auto_increment,

client_id integer UNSIGNED not null,

total float,

status enum('open', 'paid', 'lost') not null default 'open',

foreign key (client_id) references clients(client_ID),

created_at timestamp not null default current_timestamp,

modified_at timestamp not null default current_timestamp on update current_timestamp

);

Danny Alejandro fernandez gallego

student•

En vez de borrar, usas soft delete (un campo deleted_at o is_active).

Mateo Henao

student•

Mateo, las llaves foráneas son cruciales para mantener la integridad referencial entre tablas. Aseguran que los datos relacionados sean válidos, evitando inconsistencias como, por ejemplo, tener un pedido asociado a un cliente que no existe, como se explica en la clase.

Juan Diego

student•

En el contexto de bases de datos, las "llaves duras" (o "hard keys") se refieren a relaciones foráneas estrictamente definidas en la base de datos, donde la integridad referencial es mantenida automáticamente por el sistema. Esto significa que, si un registro relacionado es eliminado o modificado, se aplican reglas como "cascade" o "set null" para mantener la coherencia de los datos.

Por otro lado, las "llaves suaves" (o "soft keys") no tienen estas restricciones en la base de datos. La lógica para mantener la integridad referencial se maneja en la capa de aplicación, lo que brinda mayor flexibilidad, pero también puede ser más propenso a errores si no se gestiona adecuadamente. Esta distinción afecta cómo se diseñan y gestionan las bases de datos en función de las necesidades del proyecto.

Jesus Ivan Villalobos de la Cruz

student•

Más de una vez me lleve un regaño por insistir en qué, no todas las relaciones en la base de datos deberían estar 'harcodeadas' pensé que era por mi falta de experiencia pero hoy me di cuenta que no estaba tan equivocado.

Alberto Alcocer

teacher•

Estudia, entiende y cuando tengas claros tus ideas, defiéndelas. Bravo. Hay veces que estamos en lo correcto, hay veces que no (y aprendemos).

CARLOS ALBERTO MARTINEZ SANCHEZ

student••

¿ Jarcodiadas ?! (H ó J)

Sergio Javier Lopez Olivera

student•

Si estas en PostgreSQL y quieres hacer el ALTER para eliminar las referencias a las tablas client y product de bill y bill_product:

Maria Alejandra Luna Tito

student•

-- nombrar las tablas en plural
-- unsigned: todos los valores serán positivos
-- timestamp: epoc el nro de segundos

create table if not exists clients (
    client_id integer unsigned primary key auto_increment,
    name varchar(100) not null,
    email varchar(60) unique not null,
    phone_number varchar(15) not null,
    created_at timestamp not null default current_timestamp,
    updated_at timestamp not null default current_timestamp on update current_timestamp
);

select * from clients;

create table if not exists products (
    product_id integer unsigned primary key auto_increment,
    name varchar(20) not null,
    -- sku varchar(15) not null,
    slug varchar(200) not null unique,
    description text,
    -- price float not null,
    created_at timestamp not null default current_timestamp,
    updated_at timestamp not null default current_timestamp on update current_timestamp
);

create table if not exists bills (
    bill_id integer unsigned primary key auto_increment,
    client_id integer unsigned not null,
    total float not null,
    status enum('open', 'paid', 'lost') not null default 'open',
    created_at timestamp not null default current_timestamp,
    updated_at timestamp not null default current_timestamp on update current_timestamp
    -- foreign key (client_id) references clients(client_id) on delete cascade on update cascade
);

create table if not exists bill_products (
    bill_product_id integer unsigned primary key auto_increment,
    bill_id integer unsigned not null,
    product_id integer unsigned not null,
    quantity integer not null default 1,
    created_at timestamp not null default current_timestamp,
    updated_at timestamp not null default current_timestamp on update current_timestamp
    -- foreign key (bill_id) references bills(bill_id) on delete cascade on update cascade,
    -- foreign key (product_id) references products(product_id)
);

-- ------------------------------------------------

show tables;

insert into clients (client_id, name, email, phone_number) value (10, 'Juanito', 'juan@gmail.com', '9876543');
insert into products (name, slug) values ('cuaderno', 'slug-cuaderno');
insert into bills (client_id, total) value(10, 15.50);
insert into bill_products (bill_id, product_id, quantity) value(2, 1,12);

desc bills;

-- delete from clients;

select * from clients;
select * from bills;

update bills
set total = 19
where bill_id = 1;

update clients
set client_id = 12
where client_id = 10;

select * from bill_products;

show tables

Miguel Angel Otero Otero

student•

🔒 Relación DURA - Biblioteca con Reglas Estrictas

Imagina una biblioteca donde:

Cada libro tiene una cadena física conectada al estante
Si intentas sacar un libro que alguien está leyendo, una alarma suena y te detiene
No puedes eliminar un estante si todavía tiene libros encadenados
No puedes prestar un libro a alguien que no está registrado en el sistema

🔓 Relación SUAVE - Biblioteca con Sistema de Honor

Ahora imagina una biblioteca diferente:

Los libros tienen una etiqueta con el nombre del usuario, pero no están encadenados
Puedes escribir cualquier nombre en la etiqueta, incluso "Gandalf" o "999"
Puedes eliminar usuarios y los libros se quedan con etiquetas huérfanas
El bibliotecario (tu código) debe verificar que todo esté correcto

Edson Soto

student•

Excelente clase, que complementa la anterior, yo personalmente desconocía lo de relaciones duras y suaves en la gestión de DB, siempre creí que todo era relaciones duras. Bueno estoy aprendiendo bastante... gracias

Eduardo Johnson A.

student•

yo también.

Saludos desde Peru

Carlos Villavicencio

student•

El titulo de esta clase es similar a la de la anterior. gg

Andres Iván Guirriman Álvarez

student•

Una consulta, no es algo redundante tener las tablas bills y bill_products? Podría ser una unica tabla que tenga al cliente, el producto, la cantidad de compra, el status y el total o no? Gracias

Rod Aguilar

student•

No, porque si no estaría en contra de la primera y segunda forma.

habría duplicado del cliente, status y total

CARLOS ALBERTO MARTINEZ SANCHEZ

student••

¿ Jarcodeada ?!!

Pedro Luis Zamora Martinez

student••

estoy usando SQL donde creo una base de datos con MySQL??? se usa SQL server management studio?

Santiago Giraldo Vargas

student•

Hola Pedro, yo tuve el mismo error y si bien pude hacer casi todo lo mismo en SQL Server, lo mejor es que descargues MySQL. Revisa los comentarios de la clase 4 o 5 y ahí podrás encontrar varias referencias a videos en los que se explica bien cómo instalar.

Jorge Ivan Meza

student•

¿cuando el profesor habla de "relaciones duras" es porque hay "relaciones blandas"?

Patricio Manuel Ugarte

student•

Las relaciones blandas serían las que se establecen en la capa del modelo de la aplicación, antes de llegar a la base de datos.