When designing a set of database tables, it is important to specify which fields will be used for primary and foreign keys to clarify both in-table structure and inter-table relationships. A foreign key is a key used to link two tables together. This is sometimes called a referencing key. Foreign key is a column or a combination of columns whose values match a primary key in a different table. The relationship between 2 tables matches the primary key in one of the tables with a foreign key in the second table.
Use primary key and foreign key in mysql
In MySQL, a primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a NULL value. A table can have only one primary key. The PRIMARY KEY constraint can best be thought of as a combination of the NOT NULL and UNIQUE constraints.
CREATE TABLE airport ( AirportID smallint(5) unsigned NOT NULL, AirportCode char(3), AirportName varchar(255) NOT NULL, CityName varchar(255) NOT NULL, CountryCode char(2) NOT NULL, NumRunways INT(11) unsigned NOT NULL, NumTerminals tinyint(1) unsigned NOT NULL, PRIMARY KEY (AirportID) ) ENGINE=MYISAM;
Because the AirportID field is defined as the primary key, MySQL won’t allow duplication or NULL values in that field. This allows the database administrator to ensure that every airport listed in the table has a unique numeric value.
PRIMARY KEY constraints can be specified for either a single field or for a composite of multiple fields.
CREATE TABLE flightdep ( FlightID SMALLINT(6) NOT NULL, DepDay TINYINT(4) NOT NULL, DepTime TIME NOT NULL, PRIMARY KEY (FlightID, DepDay, DepTime) ) ENGINE=MyISAM;
A foreign key is a field in a table that matches a field of another table. A foreign key places constraints on data in the related tables that, which enables MySQL to maintain referential integrity.
We have two tables: tests and questions. Each test has zero or more questions and each questions belongs to only one test. The relationship between tests table and questions table is one-to-many, and it is established by a foreign key in the questions table specified by the id_test field. The id_test field in the questions table relates to the id_test primary key field in tests table.
The tests table is called parent table or referenced table, and the questions table is known as child table or referencing table.
A foreign key has not only one column but also a set of columns. The columns in the child table often refer to the primary key columns in the parent table.
A table may have more than one foreign key, and each foreign key in the child table can have a different parent table.
When creating a table, a foreign key can be defined in much the same way as a primary key, by using the FOREIGN KEY…REFERENCES modifier. The following example demonstrates by creating two InnoDB tables linked to each other in a one-tomany relationship:
CREATE TABLE table_name ( ... PRIMARY KEY (ID), FOREIGN KEY (TypeID) REFERENCES child_table (childTypeID) ) ENGINE=INNODB;
MySQL create foreign key syntax
CONSTRAINT constraint_name FOREIGN KEY foreign_key_name (columns) REFERENCES parent_table(columns) ON DELETE action ON UPDATE action
The following three constraints must be kept in mind when linking tables with foreign keys:
- All the tables in the relationship must be InnoDB tables. In non-InnoDB tables, the FOREIGN KEY…REFERENCES modifier is simply ignored by MySQL.
- The fields used in the foreign key relationship must be indexed in all referenced tables (InnoDB will automatically create these indexes for you if you don’t specify any).
- The data types of the fields named in the foreign key relationship should be similar. This is especially true of integer types, which must match in both size and sign.
Automatic Key Updates and Deletions Foreign keys can certainly take care of ensuring the integrity of newly inserted records.
- The CONSTRAINT clause allows you to define constraint name for the foreign key constraint. If you omit it, MySQL will generate a name automatically.
- The FOREIGN KEY clause specifies the columns in the child table that refer to primary key columns in the parent table. You can put a foreign key name after FOREIGN KEY clause or leave it to let MySQL to create a name for you. Notice that MySQL automatically creates an index with the foreign_key_name name.
- The REFERENCES clause specifies the parent table and its columns to which the columns in the child table refer. The number of columns in child table and parent table specified in the FOREIGN KEY and REFERENCES must be the same.
- The ON DELETE clause allows you to define what happens to the records in the child table when the records in the parent table are deleted. If you omit the ON DELETE clause and delete a record in the parent table that has records in the child table refer to, MySQL will reject the deletion. In addition, MySQL also provides you with actions so that you can have other options such as ON DELETE CASCADE that lets MySQL to delete records in the child table that refer to a record in the parent table when the record in the parent table is deleted. If you don’t want the related records in the child table to be deleted, you use the ON DELETE SET NULL action instead. MySQL will set the foreign key column values in the child table to NULL when the record in the parent table is deleted, with a condition that the foreign key column in the child table must accept NULL values. Notice that if you use ON DELETE NO ACTION or ON DELETE RESTRICT action, MySQL will reject the deletion.
- The ON UPDATE clause enables you to specify what happens to the rows in the child table when rows in the parent table are updated. You can omit the ON UPDATE clause to let MySQL to reject any update to the rows in the child table when the rows in the parent table are updated. The ON UPDATE CASCADE action allows you to perform cross-table update, and the ON UPDATE SET NULL action resets the values in the rows in the child table to NULL values when the rows in the parent table are updated. The ON UPDATE NO ACTION or UPDATE RESTRICT actions reject any updates.
Caution Be aware that setting up MySQL for automatic operations through ON UPDATE and ON DELETE rules can result in serious data corruption if your key relationships aren’t set up perfectly. For example, if you have a series of tables linked together by foreign key relationships and ON DELETE CASCADE rules, a change in any of the master tables can result in records, even records linked only peripherally to the original deletion, getting wiped out with no warning. For this reason, you should check (and then double-check) these rules before finalizing them.