What are Relational Schemas and What is a Primary Key

SQL Tutorials 7 min read
primary key
Blog / SQL Tutorials / What are Relational Schemas and What is a Primary Key

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”.

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 sales table

data science training

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.

2 sales made on the same date

Analogically, in the third column, the ID of a customer may appear a few times, given that some customers make more than one purchase.

Customers may appear more than once

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.

The item code may be the same if the same product was bought multiple times

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.

Every purchase number will be unique

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.

A primary key may be composed of a set of columns

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.

Row one and Row 2 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.

You can't have the same purchase number

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.

Purchase number can go from one to a few million

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.

A column whose value exists and is unique for eveery record in a table is called a primary key

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.

A primary key cannot contain null values

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.

A relational schema is a rectangle with 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 primary key 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 schema is called sales, the primary key is Purchase number and there are three other fields

This image corresponds to tabular data in the following form:

It corresponds the tabular 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:

each table can have one and only one primary key. Primary keys are unique. They cannot contain null values. Not all tables have a primary key.

Relational Schemas

To conclude, the term for representing a table with a rectangle and the fields is called a relational schema.

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.

Many tables are a database schema

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? Learn how to put theory into practice with our hands-on tutorials!

Next Tutorial: SQL Foreign Keys

Earn your Data Science Degree

Expert instructions, unmatched support and a verified certificate upon completion!

Leave a Reply

Your email address will not be published.

×
LAST CHANCE
Ready to Learn Data Science?
50% OFF