{"id":1155,"date":"2024-07-31T11:33:00","date_gmt":"2024-07-31T09:33:00","guid":{"rendered":"https:\/\/blog.sutilweb.eu\/?page_id=1155"},"modified":"2024-07-31T11:33:00","modified_gmt":"2024-07-31T09:33:00","slug":"35-restricciones","status":"publish","type":"page","link":"https:\/\/sutilweb.eu\/index.php\/databases\/sql\/35-restricciones\/","title":{"rendered":"35. Restricciones"},"content":{"rendered":"\n<p>Cuando est\u00e1s trabajando en un <strong>modelo relacional<\/strong>, y existen <strong>dependencias entre las tablas<\/strong>, no puedes eliminar dichas <strong>tablas<\/strong> tan a la ligera, e incluso, pueden ocurrir casos en que actualizar o eliminar informaci\u00f3n no lo va a permitir la base de datos.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p>En <strong>SQL,<\/strong> sobre todo en operaciones de tipo <strong><em>DELETE<\/em><\/strong> y <strong><em>UPDATE,<\/em><\/strong> podemos especificar que acciones puede realizar el <strong>sistema gestor de bases de datos<\/strong>.<\/p>\n\n\n\n<p>En <strong>MySQL<\/strong> hay 4 acciones que podemos hacer con los comandos <strong><em>DELETE<\/em><\/strong> y <strong><em>UPDATE,<\/em><\/strong> y son:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong><em>CASCADE:<\/em><\/strong> Cuando tenemos una restricci\u00f3n tipo <strong><em>CASCADE<\/em><\/strong> (cascada) lo que ocurre es que elimina o actualiza los <strong>registros<\/strong> relacionados a la <strong>tabla<\/strong> relacionada, es decir, si cambiamos un valor en la tabla que hace referencia a la llave for\u00e1nea de otra, ese valor autom\u00e1ticamente se va a ver reflejado en la otra tabla.<\/li>\n\n\n\n<li><strong><em>SET NULL<\/em><\/strong>: Cuando por poner un ejm, en un blog tenemos una categor\u00eda que deseamos borrar, los registros asociados a esa categor\u00eda pasar\u00e1n a la categor\u00eda NULL.<\/li>\n\n\n\n<li><strong><em>SET DEFAULT<\/em><\/strong>: Si en alg\u00fan momento alg\u00fan valor es <strong>llave for\u00e1nea<\/strong> se ve eliminado, el valor que van a tomar esos campos que estaban relacionados con la <strong>llave for\u00e1nea<\/strong> van a tomar el valor por defecto que hayamos especificado en ese campo en la definici\u00f3n de nuestra <strong>columna.<\/strong><\/li>\n\n\n\n<li><strong><em>RESTRICT:<\/em><\/strong> Evita la eliminaci\u00f3n o actualizaci\u00f3n del registro en la tabla principal, y solo nos permite eliminar en la tabla secundaria el registro hasta que en la tabla principal no haya ning\u00fan registro con ese valor.<\/li>\n<\/ul>\n\n\n\n<p>Podemos tener una mezcla de por ejm, tener en el <strong><em>UPDATE CASCADE<\/em><\/strong> para que se actualice en cascada, y en el <strong><em>DELETE RESTRICT<\/em><\/strong>, que de hecho son las restricciones que m\u00e1s se suelen utilizar para poder tener una integridad correcta en los datos.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Ejercicio<\/h2>\n\n\n\n<p>Vamos a crear un ejercicio para ver el funcionamiento de las restricciones.<\/p>\n\n\n\n<p><strong>Ejm<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE TABLE lenguajes (\nlenguaje_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\nlenguaje VARCHAR(30) NOT NULL\n);\n\nINSERT INTO lenguajes (lenguaje) VALUES\n('Javascript'),\n('PHP'),\n('Python'),\n('Ruby'),\n('Java'),\n('ASP');\n\nCREATE TABLE entornos (\nentorno_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\nentorno VARCHAR(30) NOT NULL );\n\nINSERT INTO entornos (entorno) VALUES\n('Frontend'),\n('Backend') ;\n\nCREATE TABLE frameworks (\nframework_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\nframework VARCHAR(50) NOT NULL,\nlenguaje INT UNSIGNED,\nentorno INT UNSIGNED,\nFOREIGN KEY (lenguaje) REFERENCES lenguajes(lenguaje_id)\nON DELETE RESTRICT ON UPDATE CASCADE\nFOREIGN KEY (entorno) REFERENCES entornos(entorno_id)\nON DELETE RESTRICT ON UPDATE CASCADE\n);\n\nINSERT INTO frameworks (framework, lenguaje, entorno) VALUES\n('React',1,1),\n('Angular',1,1),\n('Vue',1,1),\n('Svelte',1,1),\n('Laravel',2,2),\n('Symfony',2,2),\n('Flask',3,2),\n('Django',3,2),\n('On Rails',4,2),\n<\/pre>\n\n\n\n<p>Si yo intentara borrar un <strong>registro<\/strong> cuya <strong>tabla<\/strong> est\u00e9 relacionada con otra <strong>tabla,<\/strong> y existan <strong>registros,<\/strong> en ambas, no se podr\u00e1 borrar ese registro ya que ambas <strong>tablas<\/strong> est\u00e1n vinculadas. Las restricciones se definen a la hora que estamos creando nuestras tablas.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p><strong>Nota:<\/strong> en el <strong>DELETE<\/strong> es aconsejable utilizar la restricci\u00f3n <strong>RESTRICT,<\/strong> mientras que en el <strong>UPDATE<\/strong> es conveniente aplicar la restricci\u00f3n <strong>CASCADE.<\/strong><\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>Cuando est\u00e1s trabajando en un modelo relacional, y existen dependencias entre las tablas, no puedes eliminar dichas tablas tan a [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1076,"menu_order":34,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"class_list":["post-1155","page","type-page","status-publish","hentry"],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"Sutil Web","author_link":"https:\/\/sutilweb.eu\/index.php\/author\/sutilweb\/"},"uagb_comment_info":0,"uagb_excerpt":"Cuando est\u00e1s trabajando en un modelo relacional, y existen dependencias entre las tablas, no puedes eliminar dichas tablas tan a [&hellip;]","_links":{"self":[{"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/pages\/1155","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/comments?post=1155"}],"version-history":[{"count":1,"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/pages\/1155\/revisions"}],"predecessor-version":[{"id":1156,"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/pages\/1155\/revisions\/1156"}],"up":[{"embeddable":true,"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/pages\/1076"}],"wp:attachment":[{"href":"https:\/\/sutilweb.eu\/index.php\/wp-json\/wp\/v2\/media?parent=1155"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}