In this tutorial, we will use a database called “Sales” to illustrate the concept of relational schemas a little better and introduce you to the SQL primary key. The data will be stored in 4 tables – “Sales”, “Customers”, “Items”, and “Companies”.
If you are expecting the tables to be spreadsheets rather than relational databases, this article may show you a different scenario.
You can see in the picture above how these tables have a tabular shape. Let’s see how a relational schema can be applied to represent them and build the landscape piece by piece.
An Example of a Table
We care about the sales per customer of our company, and that’s why we have a “Sales” table. It tells us the number with which each purchase was registered, the date of the purchase, the respective customer ID, and the item code.
The Fields of a Table
So, in the table above, the dates of a few purchases may coincide because it is normal to sell some of the same goods on the same day. In the table below we provide an example of two such dates.
Analogically, in the third column, the ID of a customer may appear a few times, given that some customers make more than one purchase.
People may have bought many units of the same product; hence, there is a possibility to see the same item code a few times in the last column.
What is an SQL Primary Key
This reasoning does not apply to the first field, though. Each purchase is unique! In databases, this means all the numbers in this column will be different because each purchase will be assigned a distinct number.
There is a term for such type of field. A column (or a set of columns) whose value exists and is unique for every record in a table is called a primary key.
Important: Each table can have one and only one primary key.
In one table, you cannot have 3 or 4 primary keys. For instance, in our “Sales” table, purchase_number can act as a single-column primary key, and there will be no other primary keys.
If the database creators decide, the primary key of a table may be composed of a set of columns, not of just one column. In the “Sales” table, both purchase_number and date_of_purchase can be thought of as a unique-identifier pair for the data in this table.
An Example of a Multi-Column Primary Key
For instance, purchase number 1 and the purchase date, which is the 3rd of September, will form a unique pair and so will purchase number 2 and the same date, 3rd of September. This means these 2 rows are different.
Side note: Don’t overlook the fact you cannot have a combination of the same purchase number, 1, and the same date, 3rd of September, more than once in your table. Either the number or the date must be different. This is because, as we already said, a primary key must be unique.
Both ways of representation make sense. Since having a one-column primary key here would mean all purchases in our database will be recorded under a different number, this means they can go from 1 to, hopefully, a million, or two.
This logic will preserve the uniqueness of the purchases. So, we will proceed with purchase_number as a single-column primary key.
Side note: You can think of a primary key as the field or group of fields that identify the content of a table in a unique way. For this reason, the primary keys are also called the unique identifiers of a table.
The Content of a Primary Key
Another crucial feature of primary keys is they cannot contain null values. This means, in the example with a single-column primary key, there must always be a value inserted in the rows under this column. You cannot leave it blank.
Please, be careful and don’t forget this characteristic of the primary key!
How to Create a Relational Schema
Technically, to create a relational schema, we draw a table, and we place its name on top.
Then, we enlist the fields vertically (as opposed to their horizontal organization in a spreadsheet). The field that is the primary key of the table is usually quoted on top of the other fields.
What is more important - it is always underlined.
The Information You Receive
Keeping these simple rules in mind, when you spot this table, you will immediately know: It is called “Sales”; its primary key is purchase_number; and there are three other fields – date_of_purchase, customer_ID, item_code.
This image corresponds to tabular data in the following form:
Last Remark About SQL Primary Key
Please, remember that not all tables you work with will have a primary key; however, almost all tables in any database will.
Summary:
Relational Schemas
To conclude, the term for representing a table with a rectangle and the fields is called a relational schema.
When you combine the relational schemas of the tables we have in a database, this gives us a database schema. Informally, it is ok to pluralize representation of the entire database as relational schemas.
More often than not, you are likely to have a primary key in your database schema. No more than one though! You can easily distinguish it from the other fields in a relational schema by the fact that it is underlined. So, if you are interested in learning about another key you can use, the next tutorial is all about Foreign keys.
***
Eager to hone your SQL skills? Check out our SQL course.
Next Tutorial: SQL Foreign Keys
