Foreign key




Foreign key definition:

Foreign key is a primary key from one database  used in the another database.




Foreign key example:

Let’s take the library database which is composed of 2 tables (entities): Students and Cities




Foreign key

Students table contains data about the names of students (FULL_NAME), their birth places (BIRTH_PLACE) and the unique IDs (identification) sequences assigned to each student (PERSON_ID).

Cities table holds names of  cities (CITY) and cities IDs (identification) sequences assigned to each city from this table (CITY_ID).

Foreign key

PERSON_ID column keeps primary key values in the Students table because each student has its own unique ID which cannot be assigned to other student in this table.

In the Cities table primary key values has been entered in the CITY_ID (each city has its own unique ID which cannot be assigned to other city).

In order to smiplify library database management procsses w can replace in the Students table names of birth places values (BIRTH_PLACE column)  by values from CITY_ID column from the Cities table assignd to the same name of city as is in the BIRTH_PLACE column.

Foreign key

Now, BIRTH_PLACE column has become foreign key column in the Students  table becouse its contains primary key values from Cities table.

In case when we want to change name of some city  (for example we want to change  Warsaw to Warszawa which is the original spelling of this city name in Polish) we must update values only in the CITY column in one table – Cities (ID_CITY for Warsaw will not been changed because Warsaw and Warszawa are the same places).

Foreign key

In the old version of the library database (without foreign key in the Students table) in case when we would like to change some city name we must update values in both tables (Cities and Students) which is rally not optimal and unwanted action.

Foreign key

 Copyright © 2015-2016 • All Rights Reserved • database-definition.com