Skip to content Skip to main navigation Skip to footer

UIDs: Primary Key in SQL

Objectives of the Primary Key in SQL

  • Define the different types of unique identifiers (UIDs) such as primary key in SQL.
  • Define a candidate key and explain why an entity can sometimes have more than one candidate UID
  • Analyze business rules and choose the most suitable primary key in SQL from the candidates
  • Recognize and discuss the issues of identification in the real world

Primary Key in SQL: Purpose

Primary Key in SQL
  • The unique identifier (UID) is very important in relational databases.
  • It is the value or combination of values that enables the user to find that one unique item among all the rest.
  • Identifying just the right attribute, or combination of attributes and relationships, is a skill that any database designer must master.
  • The unique identifier enables you to find your record in a file, a particular card in a deck of cards, your package in a warehouse, or a specific piece of data in a database.

Primary Key vs. Composite Key

  • A UID that is a single attribute is a simple primary key in sql.
  • However, sometimes a single attribute is not enough to uniquely identify an instance of an entity.
  • If the primary key is a combination of attributes, it is called a composite key.
Primary Key in SQL

The date of performance or seat number alone does not identify a concert ticket. On any given date, there are many seats available. The same seat number is sold for many different dates.

Artificial Primary Key in SQL

  • Artificial primary keys are those that don’t occur in the natural world but are created for purposes of identification in a system.
  • People are not born with “numbers,” but a lot of systems assign unique numbers to identify people: student numbers, customer IDs, etc.
Artificial UIDs
  • A shoe has a color, a size, a style, but no truly descriptive “number.”
  • However, a shoe store will assign unique numbers to each pair of shoes so they can be uniquely identified.
Artificial UIDs

More examples: credit card numbers, social security numbers, passport numbers.

Artificial Primary Key in SQL Example

  • How can we uniquely identify a STUDENT?
  • Could we use a combination of first name and last name?
    • Only if we are sure that the combination is unique.
  • Often, it is simpler and more straightforward to create an artificial attribute and make it the unique identifier.
  • A UID can be both artificial and composite.
Artificial UID Example

A combination of first name, last name, and address would possibly be unique. However, it is not as convenient as a single ID.

Think of the business requirements: if you went to the library, or checked out equipment at the gym, or asked for a copy of your grades – is it easier to just provide a student ID or to provide name and address each time?

Primary Keys from Barred Relationships

  • Sometimes the primary key is a combination of an attribute and a relationship.
  • What is the primary key of ACCOUNT? Is it artificial key? Is it composite key?
  • Two people could have the same bank account number, but at different banks.
  • Bank to bank transfers always need the bank routing number in addition to the bank account number
UIDs from Barred Relationships

UID from Barred Relationship Intersection Entity

  • As we’ve seen before, the resolution of a M:M relationship often results in barred relationships from the intersection entity to the original ones.
  • In this example, the UID of ENROLLMENT comes from STUDENT and SUBJECT.
  • The bars on the relationships tell you this.
UID from Barred Relationship Intersection Entity

Artificial UID Intersection Entity

  • It is possible for an intersection entity to use an artificial attribute as the UID, instead of the barred relationships to the originating entities.
Primary Key in SQL
  • Each MANUFACTURER may produce one or more PRODUCTs (shoes, shirts, jeans, etc.).
  • Each PRODUCT may be produced by one or more MANUFACTURERs (Nike shoes, Adidas shoes, Levi’s jeans, etc.).
  • CATALOG ITEM resolves this many-to-many relationship.
  • An item in a catalog can be uniquely identified by the manufacturer number and the product code.
  • The relationships are not barred, because an artificial UID – catalog number – has been created instead.
Artificial UID Intersection Entity

Candidate Keys

  • Sometimes two or more possible primai keys exist.
  • For example, when you order a product from a commercial website, you will usually be assigned a unique customer code and asked to enter your email address.
  • Each of these uniquely identifies you, and each could be chosen as the primary key. These are both candidate keys.
  • Only one of the candidate keys is chosen as the actual primary key. This is called the primary key.
  • The other candidates are called secondary keys.
  • Student ID has been chosen as the primary UID in both of these STUDENT entities.
  • The first entity has one secondary UID, while the second has two secondary UIDs (one of which is composite).

The composite secondary UID (first name, last name) in the second entity may not be unique and therefore would not be chosen to be the primary UID.

Identification: Database vs. Real World

  • Unique identifiers make it possible for us to distinguish one instance of an entity from another.
  • As you will see later, these become primary keys in the database.
  • A primary key allows you to access a specific record in a database.
  • In the real world, however, it is sometimes not so easy to distinguish one thing from another.

Terminology

Key terms used in this tutorial included:

  • Artificial key in sql
  • Candidate key in sql
  • Composite key in sql
  • Primary key in sql
  • Secondary key in sql
  • Simple key in sql
  • UID in sql

Summary

In this tutorial, you should have learned how to:

  • Define the different types of unique identifiers (UIDs)
  • Define a candidate UID and explain why an entity can sometimes have more than one candidate UID
  • Analyze business rules and choose the most suitable primary UID from the candidates
  • Recognize and discuss the issues of identification in the real world

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *