Foreign Key

Foreign key is a field in a “child” table of a database that is used to refer to a record in a “parent” table of the same database. For example, if there is an AUTHOR table containing information about all authors, and a BOOK table that includes all books, then books are associated with authors with the use of foreign keys.

Foreign keys are used to cross-reference tables and allow creating referential actions, i.e. actions that allow performing operations on all connected tables at once. For example, if you delete a row in the referenced table (AUTHOR), the respective rows of the referencing table (BOOK) with a matching foreign key column should be deleted as well.

Database architecture is usually designed in a way that enables using cascading relationships between table, i.e. follows a hierarchical model in which all data is grouped by components, components by items, items by types, etc. This allows applications to receive the necessary and sufficient amount of information with the use of relatively simple and effective queries.

The way database tables store, retrieve and manage their data is defined by storage engines they use. Each database management system is equipped with its own storage engines, and each engine comes with its own set of features and benefits. For instance, MySQL-based applications usually work with two types of storage engines: InnoDB and MyISAM. While the InnoDB engine has full support for transactions and referential actions using primary and foreign keys, in some aspects it is slower than the MyISAM engine which supports no transactions but provides higher reading speed.

Handy Backup lets you make hot (live) backups of different database management systems, no matter what storage engines they use. The MySQL Backup plug-in supports MyISAM, InnoDB and all other storage engines, and can be used for not only backup purposes, but also to set up MySQL replication, and to migrate to other DBMS. For more information, please see MySQL Backup.

See also:

• Primary key
• Database storage engine
• Database architecture
• MySQL Replication Software