Cómo crear tablas y utilizar 'Foreign Keys' en MySQL

SQL offers a natural and clear way to manage databases, especially when it comes to creating tables and defining relationships between them. Understanding how the basic commands work to create tables, assign data types, set unique columns and handle relationships withforeign keys is fundamental to build an efficient and organized structure.

How to create tables correctly in MySQL?

The basic command to create a table in MySQL starts with:

CREATE  TABLE IF NOT EXISTS table_name  (  column DataType Constraints,  ...);

When creating a table, it is advisable to clearly name the columns according to what they contain, using specific nouns:

• Use descriptive names such as ProductID rather than simply ID.
• Clearly consider the appropriate data types (VARCHAR, FLOAT, INTEGER, etc.).

What is the importance of the slug field and why make it unique?

The slug field is especially relevant for improving URL accessibility. It should be kept unique because:

• It facilitates SEO optimization on web pages.
• It provides self-descriptive links, easy to identify.

Practical example of creating a slug field:

slug  VARCHAR(200) NOT  NULL UNIQUE

How to handle Text fields and length restrictions in MySQL?

When you expect to store long or unknown length texts, use the TEXT data type:

• Ideal for descriptions, multilingual content or extended characters.

description  TEXT  NULL

What are 'Foreign Keys' and how to configure them?

A foreign key creates a strict relationship between tables, allowing to maintain referential integrity:

• Clearly defines which records depend on others.
• Avoids inconsistencies when modifying or deleting related data.

Simple example:

FOREIGN  KEY (ClientID) REFERENCES Clients(ClientID)

How to act when we need dynamic modifications using ENUM?

The ENUM data type allows you to define specific states for a column:

• Clearly defined limited options.
• A default value is automatically assigned if necessary(OPEN).

status ENUM('OPEN', 'PAID', 'LOST') NOT  NULL DEFAULT 'OPEN'

Note that modifying an ENUM at a later time may require direct adjustments to the database, as well as previous updates to existing data.

What is the value of using 'cascade' on foreign keys?

Using ON UPDATE CASCADE and ON DELETE CASCADE on foreign keys makes it possible to:

• Automatically update related fields when the primary key is changed.
• Automatically delete related records when deleting the main record.

However, it must be done cautiously, as the example shows:

FOREIGN  KEY (ClientID) REFERENCES Clients(ClientID)ON UPDATE CASCADEON DELETE CASCADE

How to check existing structures in created tables?

To check the current creation and structure of a table, use:

SHOW CREATE  TABLE table_name;

This instruction clearly exposes the keys and constraints applied.

We invite you to put this knowledge into practice by creating your own tables and relationships. Have you encountered any particular challenge implementing these commands? Share your experience and any questions in the comments.