“A foreign key is one or more columns in a child table that refers to a primary key of a parent table. The foreign and primary keys are the same values, though some may repeat in the child table. The parent table is also called the reference table. The foreign key in the child table references the primary key in the referenced table (parent table). That is, each foreign key value in the child table is the same as a primary key value in the parent table. There may be some repetitions of foreign key values in the child table. Still, each foreign key value in the child table is a primary key in the referenced table. The link between both tables is the foreign key.
If both tables are well designed, then the foreign key relation eliminates Transitive Dependency. See the explanation of transitive dependency below. This article explains foreign keys in relation to transitive dependency and relational database.”
Illustration
Consider a Sales table in what is known as the Second Normal Form. In the table, employees sell to known individual customers (persons). The table is as follows:
Sales Table
In the first row, a customer with customerID 1, for a shop, in the person of Peter Smith, with phone number 444-4444, address “1, road1, townA”, city Clifton, of the state New Jersey, postal code NJ 10265, came into the shop and bought products (not indicated). This customer was served by the employee (salesman), Jacob Jones. This sale has ID 1. The saleID column is the primary key for this table. This table is currently in what is known as the Second Normal Form. The rest of the other rows are similarly explained.
Transitive Dependency
Note: A column can be referred to as an attribute. In each row, the saleID depends on the attributes: dateSold, customerID, customerName, phone, address, city, region, postalCode, country, and employee. That is fine. The customerID depends on the attributes: customerName, phone, address, city, region, postalCode, and Country. These dependees of the customerID do not determine the saleID or dateSold or employee. If the customer of ID 1 came to the shop one week later, he would have met a different employee on a different date, which would mean a different saleID.
So these dependees of the customer have to be pulled out of this big table, to form a different smaller table. A copy of the customerID remains in the original table since the saleID depends on the customerID. The new Sales table is now called the child table. A copy of the customerID goes with the dependees since it depends on the dependees. The two new tables are:
Customers Table
Sales Table
The Customers table is now a parent or reference table. It has the customer names and their credentials. The customerID identifies each of its rows. It is shorter than the original Sales table by 6 rows. Repetition (redundancy) has been removed in the Customers table. The Customers table is now in what is known as the Third Normal Form. If a suitable name could not be found for the customer’s table, then something went wrong with the reasoning.
The Sales table remains as it was, but lacking some columns. It, too, is now in what is called the Third Normal Form. The saleID still identifies the same rows but with some cells removed.
Note: in the Customers table, customerID is the primary key. In the new Sales table, customerID is a foreign key. It links both new tables. However, in the Sales table, it does not depend on the saleID; the saleID depends on it.
Transitive dependency occurs when one non-prime attribute is dependent on another non-prime attribute or attribute. By splitting the original table into two tables, a transitive dependency is removed.
The table notation for the original table is:
The table notation for the two new tables is:
and
Each primary key has a single underline. The foreign key, customerID, in the Sales table has a dash underline.
Conclusion
A foreign key is one or more columns in a child table that refers to a primary key of a parent table. The foreign and primary keys are the same values, though some may repeat in the child
table. The parent table is also called the reference table. The foreign key in the child table references the primary key in the referenced table (parent table). That is, each foreign key value in the child table is the same as a primary key value in the parent table. There may be some repetitions of foreign key values in the child table. Still, each foreign key value in the child table occurs as a primary key in the referenced table. The link between both tables is the foreign key. The child table is also called the referencing table.
