{"id":29235,"date":"2024-04-22T03:18:54","date_gmt":"2024-04-22T02:18:54","guid":{"rendered":"https:\/\/nelkodev.com\/blog\/dominando-las-claves-foraneas-en-mysql-guia-paso-a-paso\/"},"modified":"2024-06-03T17:45:16","modified_gmt":"2024-06-03T16:45:16","slug":"dominando-las-claves-foraneas-en-mysql-guia-paso-a-paso","status":"publish","type":"post","link":"https:\/\/nelkodev.com\/en\/blog\/mastering-foreign-keys-in-mysql-step-by-step-guide\/","title":{"rendered":"Mastering Foreign Keys in MySQL: Step-by-Step Guide"},"content":{"rendered":"<p>Foreign keys are one of the most essential components in relational database design. They allow referential integrity to be maintained between tables and ensure that the relationships between them remain correct and efficient. This article is a detailed guide on how to create and delete foreign keys in MySQL, designed to provide you with a deep and practical understanding of the topic.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_80 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #ffffff;color:#ffffff\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #ffffff;color:#ffffff\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-foreign-keys-in-mysql-step-by-step-guide\/#%C2%BFQue_es_una_Clave_Foranea\" >What is a Foreign Key?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-foreign-keys-in-mysql-step-by-step-guide\/#Paso_1_Preparacion_del_Ambiente\" >Step 1: Environment Preparation<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-foreign-keys-in-mysql-step-by-step-guide\/#Crear_las_Tablas_de_Ejemplo\" >Create the Example Tables<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-foreign-keys-in-mysql-step-by-step-guide\/#Paso_2_Crear_una_Clave_Foranea\" >Step 2: Create a Foreign Key<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-foreign-keys-in-mysql-step-by-step-guide\/#Sintaxis_para_Crear_una_Clave_Foranea\" >Syntax to Create a Foreign Key<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-foreign-keys-in-mysql-step-by-step-guide\/#Implementacion_Practica\" >Practical Implementation<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-foreign-keys-in-mysql-step-by-step-guide\/#Paso_3_Validar_la_Relacion\" >Step 3: Validate the Relationship<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-foreign-keys-in-mysql-step-by-step-guide\/#Insertar_Datos_Correctamente\" >Insert Data Correctly<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-foreign-keys-in-mysql-step-by-step-guide\/#Insertar_Datos_Incorrectos\" >Insert Incorrect Data<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-foreign-keys-in-mysql-step-by-step-guide\/#Paso_4_Eliminar_una_Clave_Foranea\" >Step 4: Delete a Foreign Key<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-foreign-keys-in-mysql-step-by-step-guide\/#Sintaxis_para_Eliminar_una_Clave_Foranea\" >Syntax to Delete a Foreign Key<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-foreign-keys-in-mysql-step-by-step-guide\/#Implementacion_Practicai\" >Practical Implementationi<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-foreign-keys-in-mysql-step-by-step-guide\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"%C2%BFQue_es_una_Clave_Foranea\"><\/span>What is a Foreign Key?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>A foreign key is a column or set of columns in a database table that is used to establish and focus a &quot;referencing&quot; relationship with the column or columns of another table. In essence, the foreign key is the bridge that connects two tables and is essential for maintaining data consistency.<\/p>\n<p>For example, suppose you have a table called <code>Customers<\/code> and a table called <code>Orders<\/code>. Each order is made by a specific customer. Therefore, you can include a column in <code>Orders<\/code> that is a foreign key referencing the <code>ID<\/code> of the client in the table <code>Customers<\/code>. This ensures that each order is linked to an existing customer.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Paso_1_Preparacion_del_Ambiente\"><\/span>Step 1: Environment Preparation<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Before starting to create foreign keys, you need to prepare your work environment. Make sure you have access to a MySQL server and have the necessary credentials to create and modify databases.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Crear_las_Tablas_de_Ejemplo\"><\/span>Create the Example Tables<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>To demonstrate how to work with foreign keys, we will create two simple tables: <code>Customers<\/code> y <code>Orders<\/code>.<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">CREATE TABLE Customers ( ID int NOT NULL AUTO_INCREMENT, Name varchar(255) NOT NULL, Email varchar(255) UNIQUE NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE Orders ( OrderID int NOT NULL AUTO_INCREMENT, CustomerID int, OrderDate date NOT NULL, Amount decimal(10,2) NOT NULL, PRIMARY KEY (OrderID) );<\/code><\/pre>\n<h2><span class=\"ez-toc-section\" id=\"Paso_2_Crear_una_Clave_Foranea\"><\/span>Step 2: Create a Foreign Key<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Once you have your tables ready, the next step is to link them using a foreign key.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Sintaxis_para_Crear_una_Clave_Foranea\"><\/span>Syntax to Create a Foreign Key<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>The general syntax for adding a foreign key to an existing table is as follows:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">ALTER TABLE child_table ADD CONSTRAINT fk_name FOREIGN KEY (child_column) REFERENCES parent_table (parent_column);<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Implementacion_Practica\"><\/span>Practical Implementation<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Let&#039;s add a foreign key to the table <code>Orders<\/code> to connect each order with a customer:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">ALTER TABLE Orders ADD CONSTRAINT customer_fk FOREIGN KEY (CustomerID) REFERENCES Customers (ID);<\/code><\/pre>\n<p>This command modifies the table <code>Orders<\/code> and establishes a relationship where <code>ClientID<\/code> in <code>Orders<\/code> points to <code>ID<\/code> In the table <code>Customers<\/code>.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Paso_3_Validar_la_Relacion\"><\/span>Step 3: Validate the Relationship<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>After creating the foreign key, it is essential to validate that the relationship has been established correctly. Try inserting data into tables to ensure referential integrity is maintained.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Insertar_Datos_Correctamente\"><\/span>Insert Data Correctly<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<pre><code class=\"&quot;language-sql&quot;\">INSERT INTO Customers (Name, Email) VALUES (&#039;Juan P\u00e9rez&#039;, &#039;juan.perez@email.com&#039;); INSERT INTO Orders (CustomerID, OrderDate, Amount) VALUES (1, &#039;2023-01-01&#039;, 100.00);<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Insertar_Datos_Incorrectos\"><\/span>Insert Incorrect Data<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Try to insert an order with a <code>ClientID<\/code> nonexistent:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">INSERT INTO Orders (CustomerID, OrderDate, Amount) VALUES (999, &#039;2023-01-01&#039;, 100.00);<\/code><\/pre>\n<p>This attempt should fail, displaying an error indicating that referential integrity is being violated.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Paso_4_Eliminar_una_Clave_Foranea\"><\/span>Step 4: Delete a Foreign Key<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>There may come a time when you need to delete a foreign key. Either because the structure of your database has changed or because the relationship is no longer necessary.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Sintaxis_para_Eliminar_una_Clave_Foranea\"><\/span>Syntax to Delete a Foreign Key<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>To delete a foreign key:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">ALTER TABLE child_table DROP FOREIGN KEY fk_name;<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Implementacion_Practicai\"><\/span>Practical Implementationi<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>To delete the foreign key we created:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">ALTER TABLE Orders DROP FOREIGN KEY customer_fk;<\/code><\/pre>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Foreign keys are essential for managing data integrity in relational databases. Learning how to create and manage these keys is essential for any developer working with databases. I hope this guide has been useful to you in understanding how to work with foreign keys in MySQL. If you have any questions or need more information, don&#039;t hesitate to <a href=\"https:\/\/nelkodev.com\/en\/contact\/\">contact me<\/a>.<\/p>\n<p>Remember that you can always find more resources and guides at <a href=\"https:\/\/nelkodev.com\/en\/\">NelkoDev<\/a>. Happy coding!<\/p>","protected":false},"excerpt":{"rendered":"<p>Las claves for\u00e1neas son uno de los componentes m\u00e1s esenciales en el dise\u00f1o de bases de datos relacionales. Permiten mantener la integridad referencial entre tablas y aseguran que las relaciones entre ellas se mantengan correctas y eficientes. Este art\u00edculo es una gu\u00eda detallada sobre c\u00f3mo crear y eliminar claves for\u00e1neas en MySQL, dise\u00f1ada para proporcionarte [&hellip;]<\/p>","protected":false},"author":1,"featured_media":29236,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[420,2185],"tags":[205,295,1159,2189,1289,2179,358,48,352,528],"class_list":["post-29235","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-curso-mysql","tag-blog","tag-claves","tag-constraints","tag-curso","tag-dominando","tag-foraneas","tag-guia","tag-las","tag-mysql","tag-paso"],"_links":{"self":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29235","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/comments?post=29235"}],"version-history":[{"count":0,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29235\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media\/29236"}],"wp:attachment":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media?parent=29235"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/categories?post=29235"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/tags?post=29235"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}