2.2 Understanding Tables and Keys

 

Before we talk about how to create a table, it is important that we understand tables and how they can be connected to each other using foreign keys. The key feature of a relational database is the relationships we can define between tables to connect the data within them.

 

Tables

---

Tables are the foundational building block of a database. They are structures that will be defined to hold our data. Tables consist of columns and rows (or records). Consider the following Person table:

person_id	first_name	last_name	middle_name	birthdate	created_by	date_created	modified_by	date_modified
1	SYSTEM	SYSTEM			1	2019-12-13 10:05:20.023263	1	2019-12-13 10:05:20.023263
2	Tanner	Crook			1	2019-12-13 10:06:20.023263	1	2019-12-13 10:06:20.023263

You can see that the data is stored by row and column, creating fields where individual values can be stored. This concept should be fairly straightforward to most. However, a concept that is probably new to database users is the PRIMARY KEY object.

In databases, columns are identified by their names which can be thought of as variables. Consider a traditional spreadsheet editor: Along the top of the interface, columns are identified by letters. In databases, since we are defining the table, we get to determine the unique identifier for the column.

 

Primary Keys

---

A primary key is a column of a table that acts as a unique identifier for rows in the table. Consider a traditional spreadsheet editor: Built into the interface and system are primary keys that we use to identify rows and are labeled as numbers that you cannot edit. In databases we get to define the unique column by designating it as a PRIMARY KEY. Most of the time, the primary key is the first column in the table, and has a reference to ID in the column name. In the Person table, our primary key will be person_id. If we ever wanted to select a single row, without the possibility of selecting more than one, we could use the value in person_id. Additionally, if we want another table to reference the Person table (for example tie a row in the Contact table to the Person table), we would use the PRIMARY KEY person_id value to do so.

 

Foreign Keys

---

As discussed in Chapter 1, the power of a relational database system comes from the ability to tie data from two tables together. In other words, create relationships between two tables. To accomplish this, we use a foreign key.

A Foreign Key is a key from a foreign table that exists in a table to connect the row to a row in the foreign table.

Take a look at the two tables below that show the Person table with its primary key marked and the Email table with the foreign key that references person. It is this Primary Key and Foreign Key Combo that creates the relationship between Person and Email. We use this relationship to determine which email(s) belongs to which person.