In the context of relational databases, a foreign key is a referential constraint between two tables. The foreign key identifies a column or a set of columns in one (referencing) table that refers to a set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL). This way references can be made to link information together and it is an essential part of database normalization. Multiple rows in the referencing table may refer to the same row in the referenced table. Most of the time, it reflects the one (master table, or referenced table) to many (child table, or referencing table) relationship.

 

For example, we will use the Emp and Hist tables in the emp.mdb sample database that can be found in C:\Users\Public\Documents\Ecrion\Data Aggregation Server 2011\Samples\Database Samples.

 

 

The ID column in the Emp table is a primary key and as you can see is referenced in the Hist table. That means that the ID column in the Hist table is a foreign key.

To set a foreign key in Data Architect, right click on the table containing the column to be set as foreign key (here is Hist table) and select Edit Datasource.

The following dialog will open:

 

 

 

Check the Emp(EMP_ID:ID) field as foreign key and click OK.

 

You can see that a link has been added representing the relationship between the two tables:

 

 

 

In Data Architect, the relationship that was created by setting the foreign key acts like an INNER JOIN. This operation creates a new result table by combining every record in Emp table with every record in Hist table based upon the join-predicate (Emp.ID=Hist.ID).

 

If you want to see how this relationship works, connect the fields into the output like in the image below:

 

 

OUTPUT VIEW:

 

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<ns:root xmlns:ns="http://www.tempuri.org/XML">

       <ns:emp first-name="John" last-name="Brown">

               <ns:hist department="Accounting" hours-worked="300"/>

               <ns:hist department="Sales" hours-worked="500"/>

               <ns:hist department="Development" hours-worked="3000"/>

               <ns:hist department="Accounting" hours-worked="100"/>

       </ns:emp>

       <ns:emp first-name="Mary" last-name="Jane">

               <ns:hist department="Accounting" hours-worked="1000"/>

               <ns:hist department="Sales" hours-worked="8000"/>

               <ns:hist department="Development" hours-worked="5000"/>

       </ns:emp>

       <ns:emp first-name="Arthur" last-name="Clark"/>

</ns:root>

 

 

You can see that for every employee ID in the Emp table were selected the data from FIRST_NAME and LAST_NAME columns and for the corresponding IDs in the Hist table were selected the DEPARTEMENT each employee worked and the HOURS_WORKED.