+1-617-874-1011 (US)

  +44-117-230-1145 (UK)

MySQL Foreign Keys

Primary keys fill in as exceptional identifiers for the records in a table, while outside keys are utilized to connection related tables together. When planning a lot of database tables, it is essential to determine which fields will be utilized for essential and outside keys to explain both in-table structure and between table connections.

About Foreign Keys

The major premise of a social database framework like MySQL is its ability to make connections between the tables that make up the database. By making it conceivable to effortlessly relate records in various tables to each other, a RDBMS makes it conceivable to dissect information in various ways while at the same time keeping it sorted out in an efficient manner, with negligible repetition.

These connections are overseen using outside keys, basically, fields that have a similar importance in every one of the tables in the relationship and that fill in as purposes of shared characteristic to connection records in various tables together. A remote key relationship could be balanced (a record in one table is connected to one and just one record in another table) or one-to-many (a record in one table is connected to numerous records in another table).

MySQL Foreign Keys


FIGURE 1 A coordinated connection between tables


Remote keys are just upheld on InnoDB tables.

Figure 1 delineates a balanced relationship: an administration and its related portrayal, with the connection between the two oversaw by means of the one of a kind ServiceID field.

Figure 2 delineates a one-to-numerous relationship: a writer and his or her books, with the connection between the two kept up by means of the one of a kind AuthorID field.

MySQL Foreign Keys


FIGURE 2 A one-to-numerous connection between tables

When making a table, a remote key can be characterized similarly as an essential key by utilizing the FOREIGN KEY… REFERENCES modifier. The accompanying model shows by making two InnoDB tables connected to one another in a one-to-numerous relationship by the flying machine type identifier:

 mysql> CREATE TABLE aircrafttype (
 - > AircraftTypeID smallint(4) unsigned NOT NULL AUTO_INCREMENT,
 - > AircraftName varchar(255) NOT NULL,
 - > PRIMARY KEY (AircraftTypeID)
 Question OK, 0 lines influenced (0.61 sec)
 mysql> CREATE TABLE air ship (
 - > AircraftID smallint(4) unsigned NOT NULL AUTO_INCREMENT,
 - > AircraftTypeID smallint(4) unsigned NOT NULL,
 - > RegNum char(6) NOT NULL,
 - > LastMaintEnd date NOT NULL,
 - > NextMaintBegin date NOT NULL,
 - > NextMaintEnd date NOT NULL,
 - > PRIMARY KEY (AircraftID),
 - > UNIQUE (RegNum),
 - > INDEX (AircraftTypeID),
 - > FOREIGN KEY (AircraftTypeID)
 - > REFERENCES aircrafttype (AircraftTypeID)
 0 lines influenced (0.45 sec)

In this model, the aircraft.AircraftTypeID field is an outside key connected to the aircrafttype.AircraftTypeID essential key. Note the way wherein this relationship is indicated in the FOREIGN KEY… REFERENCES modifier. The FOREIGN KEY part indicates one cut off of the association (the field name in the present table), while the REFERENCES part determines the opposite cut off of the association (the field name in the referenced table).

Keywords: MYSQL Tutorials, Data Relations homework help, SQL homework help, SQL project Help, Oracle Homework Help, XML homework help, XML and Relational Algebra assignment help, SQL Queries and Updates homework assignment help, XML Queries and Transformations tutoring help, Relational Database Design concept, Higher-Level Design help in UML and ERD, Data Warehousing and Data Mining, help with SQL 2008 database, support for Database help, Microsoft access and mysql database programmer, database development and xml database support, homework helper for asp database and uml modeling, uml sequence diagram and uml class diagram, database design development and software SQL server 2008.