Business Intelligence Dictionary: Primary Keys and Foreign Keys Explained | NSBI

Keys are a special kind of database fields that are designed to support a relational design where tables can be related to one another.

Primary Key | Definition

NSBI Dictionary defines a Primary Key as a logical structure of field or group of fields that uniquely identifies each record within a table. Primary keys must be unique and cannot be empty (cannot be null).

Foreign Key | Definition

Primary keys are used to identify uniquely records in a single table, but they are also used to connect tables together.

NSBI Dictionary defines a Foreign Key as a replica of the Primary Key of Table X, that is introduced for relational purposes to Table Y. It may be non-unique and does not necessary include a value (can be null).

NSBI Tutorials aim to make Business Intelligence (BI) and Data Warehousing (DWH) attractive to non-technical people as well as to those who are now entering the field and are excited by the numerous ways data is changing our world. NSBI Tutorials are written and delivered by Nick Shopov, (BI Software Developer & DWH Consultant).

Primary Keys and Foreign Keys | Example

Let's create a single table that will include all our "Employees" information. This table includes the fields "Employee ID Num", "Name", "Location", "Position". In this table "Employee ID Num " may be considered a Primary Key, since it uniquely identifies each employee (each table row) and each employee has a national ID number.

Now let's introduce a second table called "Sales". This table includes the following fields: "SalesRep", "Product", "Quantity", "Price". In this table "SalesRep" is the Foreign Key, which is de facto equal to the "Employee ID Num" from table "Employees". It is only used to relate the two tables together since the Foreign Key uniquely identifies an employee and hence we can conclude that a specific employee made a specific sale.

Now we can identify that Jack has sold 1 TV and 1 Laptop for the total for 1,699.

Important Design Notes:

1. It is a good case practice (GCP) that Primary and Foreign Keys are always an Integer value and do not have business meaning, i.e. there is an extra field added that serves as an incremental Primary Key.

2. It is a GCP that Primary Key field name match across different tables, i.e. "SalesRep" is actually renamed to "Employee ID Num".

3. Food for thought: What would be the Primary Key of "Sales" table?

4. Choosing a Primary Key in a real case scenario is a complex task that is out of scope for this article. Check this article on normalization.

We leave it as it is for simplicity.

Tags: primary key, foreign key, nsbi-dict