Want to keep learning?

This content is taken from the Raspberry Pi Foundation & National Centre for Computing Education's online course, Introduction to Databases and SQL. Join the course to learn more.

Primary keys

Every table in a DBMS should have one field that uniquely identifies each record. A table containing products might use a product number to identify each record. The product number is unique to the product. When you search for a product using its unique product number, only the specific product you are searching for should be returned.

Computer sales database

In your computer sales database, the manufacturer_id is the primary key in the manufacturer table, model_id is the primary key in the model table, and sale_id is the primary key in the sales table. The primary keys are used to refer to a specific record within a table.

Without a primary key, updating or deleting specific records in a table becomes difficult, because there is no specific way to refer to the records you want to delete or update. For example, if you were keeping track of stock and you had two identical computers for sale, each computer would need a separate record so that you could track the sale of each one.

The primary key field can be created or selected from an existing field as long as it meets the conditions below:

  • No two records can have the same primary key data
  • The primary key value should never be re-used; if a record is deleted from the table, it should not be allocated to a new record
  • A primary key value must not be modified once it has been created
  • A primary key must have a value; it cannot be NULL

Searching

The primary key also acts as a reference to the record and offers a simple way for the DBMS to search, sort, and access the records.

If you receive a customer enquiry about a Raspberry Pi computer, some of the questions you may ask are:

  • Do you know the model number you are looking for?
  • What CPU speed would you like?
  • Is there a minimum number of cores you would like?
  • Would you like WiFi?

It would be far quicker if the customer gave you the model_id number. You can then be very precise about the model (the record) it returns.

Next steps

In previous steps, you have learnt how to search a database using SQL statements, and how to use operators for more advanced searching. If you had any problems with the quiz in the previous step, use the comments section below to ask for help.

In this step, you also started to learn about the importance of primary keys within tables; there will be more on this topic in later steps.

In the next steps you will learn about how to use SQL statements to change the data within your computer sales database.

Share this article:

This article is from the free online course:

Introduction to Databases and SQL

Raspberry Pi Foundation