CREAR UNA TABLA MYSQL con primario key y extranjero key ejemplo de código

Este equipo especializado pasados varios días de investigación y de juntar de información, dieron con la respuesta, nuestro deseo es que te sea de utilidad en tu trabajo.

Ejemplo 1: extranjero key mySql

CREATETABLEparent(
    id INTNOTNULL,PRIMARYKEY (id))ENGINE=INNODB;CREATETABLEchild (
    id INT,
    parent_id INT,INDEXpar_ind (parent_id),FOREIGNKEY (parent_id)REFERENCESparent(id)ONDELETECASCADE)ENGINE=INNODB;

Ejemplo 2: clave externa de MySQL

Here is the basic syntax of defining a foreign key constraint in the CREATETABLEorALTERTABLEstatement:[CONSTRAINT constraint_name]FOREIGNKEY[foreign_key_name](column_name,...)REFERENCESparent_table(colunm_name,...)[ONDELETE reference_option][ONUPDATE reference_option]
In this syntax:

First, specify the name of foreign key constraint that you want to create after the CONSTRAINT keyword.If you omit the constraint name, MySQL automatically generates a name for the foreign key constraint.

Second, specify a list of comma-separated foreign key columns after the FOREIGNKEY keywords. The foreign key name is also optional and is generated automatically if you skip it.

Third, specify the parent table followed by a list of comma-separated columns to which the foreign key columns reference.Finally, specify how foreign key maintains the referential integrity between the child andparent tables by using the ONDELETEandONUPDATE clauses.  The reference_option determines action which MySQL will take when values in the parent key columns are deleted (ONDELETE)orupdated (ONUPDATE).

MySQL has five reference options:CASCADE,SETNULL,NOACTION,RESTRICT,andSETDEFAULT.CASCADE:if a row from the parent table is deleted or updated, the values of the matching rows in the child table automatically deleted or updated.SETNULL:if a row from the parent table is deleted or updated, the values of the foreign key column (or columns) in the child table are set to NULL.RESTRICT:if a row from the parent table has a matching row in the child table, MySQL rejects deleting or updating rows in the parent table.NOACTION: is the same asRESTRICT.SETDEFAULT: is recognized by the MySQL parser. However, this action is rejected by both InnoDB andNDB tables.
In fact, MySQL fully supports three actions:RESTRICT,CASCADEandSETNULL.If you don’t specify the ONDELETEandONUPDATE clause, the default action is RESTRICT.

MySQL FOREIGNKEY examples
Let’s create a newdatabase called fkdemo for the demonstration.CREATEDATABASE fkdemo;USEfkdemo;RESTRICT&NOACTION actions
Inside the fkdemo database, create two tables categories andproducts:CREATETABLEcategories(
    categoryId INTAUTO_INCREMENTPRIMARYKEY,
    categoryName VARCHAR(100)NOTNULL)ENGINE=INNODB;CREATETABLEproducts(
    productId INTAUTO_INCREMENTPRIMARYKEY,
    productName varchar(100) not null,
    categoryId INT,CONSTRAINT fk_category
    FOREIGNKEY (categoryId)REFERENCEScategories(categoryId))ENGINE=INNODB;
The categoryId in the products table is the foreign key column that refers to the categoryId column in the  categories table.

Because we don’t specify any ONUPDATEandONDELETE clauses, the default action is RESTRICTfor both update and delete operation.

Reseñas y valoraciones del artículo

---

---

Utiliza Nuestro Buscador