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