|Published (Last):||16 July 2013|
|PDF File Size:||8.11 Mb|
|ePub File Size:||14.67 Mb|
|Price:||Free* [*Free Regsitration Required]|
MySQL supports foreign keys, which permit cross-referencing related data across tables, and foreign key constraints, which help keep the related data consistent. A foreign key relationship involves a parent table that holds the initial column values, and a child table with column values that reference the parent column values. A foreign key constraint is defined on the child table. Foreign key constraint usage is described under the following topics in this section:. Conditions and Restrictions.
Referential Actions. Foreign Key Constraint Examples. Adding Foreign Key Constraints. Dropping Foreign Key Constraints. Foreign Key Checks. Foreign Key Definitions and Metadata. Foreign Key Errors. For InnoDB tables, a constraint name is generated automatically. Otherwise, a constraint name is generated automatically. Foreign key constraints are subject to the following conditions and restrictions:.
Parent and child tables must use the same storage engine, and they cannot be defined as temporary tables. Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same.
For nonbinary character string columns, the character set and collation must be the same. MySQL supports foreign key references between one column and another within a table. A column cannot have a foreign key reference to itself. MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order.
Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint.
InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are the first columns in the same order. NDB requires an explicit unique key or primary key on any column referenced as a foreign key.
Index prefixes on foreign key columns are not supported. InnoDB does not currently support foreign keys for tables with user-defined partitioning. This includes both parent and child tables. A table in a foreign key relationship cannot be altered to use another storage engine. To change the storage engine, you must drop any foreign key constraints first. Referential actions include:. The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table.
As of NDB 7. Bug , Bug InnoDB performs cascading operations using a depth-first search algorithm on the records of the index that corresponds to the foreign key constraint. This simple example relates parent and child tables through a single-column foreign key:. One foreign key references a two-column index in the product table.
The other references a single-column index in the customer table:. The foreign key can be self referential referring to the same table. Otherwise, a constraint name was generated internally, and you must use that value.
Typically, you leave this variable enabled during normal operation to enforce referential integrity. Previously, the setting was ignored for NDB tables and all such checks were enforced Bug Dropping a table that is referenced by a foreign key constraint. When you drop a table, constraints defined on the table are also dropped.
Reloading tables in different order than required by their foreign key relationships. For example, mysqldump produces correct definitions of tables in the dump file, including foreign key constraints for child tables. This enables you to import the tables in any order in case the dump file contains tables that are not correctly ordered for foreign keys.
Recreating a table that was previously dropped returns an error if the table definition does not conform to the foreign key constraints that reference the table. The table must have the correct column names and types.
It must also have indexes on the referenced keys. If these requirements are not satisfied, MySQL returns Error that refers to errno: in the error message, which means that a foreign key constraint was not correctly formed. Altering a table returns an error errno: if a foreign key definition is incorrectly formed for the altered table. Dropping an index required by a foreign key constraint.
The foreign key constraint must be removed before dropping the index. Creating a foreign key constraint where a column references a nonmatching column type. It is permitted to drop a database that contains tables with foreign keys that are referenced by tables outside the database. It is permitted to drop a table with foreign keys referenced by other tables. An example of a query against this table is shown here:. Example queries are show here:.
To hide information about parent tables, include the appropriate condition handlers in application code and stored programs. Data Definition Statements. LIKE Statement. Silent Column Specification Changes. Data Manipulation Statements. The Subquery as Scalar Operand. Comparisons Using Subqueries.
Rewriting Subqueries as Joins. Restrictions on Subqueries. Transactional and Locking Statements. Statements That Cause an Implicit Commit. Restrictions on XA Transactions. Replication Statements. Variables in Stored Programs. Local Variable Scope and Resolution. Flow Control Statements. Restrictions on Server-Side Cursors. Restrictions on Condition Handling. Database Administration Statements. Account Management Statements.
Table Maintenance Statements. Plugin and User-Defined Function Statements. Other Administrative Statements. Errors, Error Codes, and Common Problems. MySQL 5.
Tutorial de estructura y diseño de bases de datos
Prueba Lucidchart. Luego enumera los tipos de datos que quieres almacenar y las entidades o personas, cosas, ubicaciones y eventos que esos datos describen, del siguiente modo:. Para convertir tus listas de datos en tablas, comienza creando una tabla para cada tipo de entidad, como productos, ventas, clientes y pedidos. Cada fila de una tabla se llama "registro". Con el fin de que los datos sean consistentes de un registro al siguiente, asigna el tipo de datos apropiado a cada columna.
Suscribirte al RSS