6 Difference Between Primary And Secondary Key

SHARE

What Is A Primary Key?

A primary key, also referred to as a primary keyword, is a key in relational database that is unique for each record. It is a unique identifier such as a driver license number, Social security number, telephone number (including area code), or vehicle identification number. A relational database must always have one and only one primary key. A primary key typically appears to be as columns in relational database tables. Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are referred to as a composite key.

Facts About Primary Key

  • A primary key is used to ensure data in the specific column is unique.
  • It uniquely identifies a record in the relational database table.
  • Only one primary key is allowed in a table in a table.
  • It is a combination of UNIQUE and Not Null constraints.
  • It does not allow NULL values cannot be deleted from the parent table.
  • Its constraint can be implicitly defined on the temporary tables.
  • Examples of primary keys include: Unique last name, Social security number, online username.

What Is A Secondary Key?

A secondary key represents a secondary value that is unique for each record that can be used to identify the record. You may have a primary key that is system generated and a secondary key that comes from the source or by some other process. You might have an invoice number that is generated by the system but you have a client specific identifier that is guaranteed unique. This is secondary key.  

In other words, a secondary key provides a secondary reference point for objects whose primary keys do not adequately distinguish them for reference purposes. In the event that a primary key is not enough to distinguish an object, a secondary key can be used to render that object unique. It is processed and sorted in relation to a primary key, clarifying search terms so that only desired results appear when a table is consulted. This creates distinct, cleaner databases.

Facts About Secondary Key

  • A secondary key provides a secondary reference point for objects whose primary keys do not adequately distinguish them for reference purposes.
  • It is used for identification of rows but not usually unique.
  • We can have multiple secondary key per table.
  • In the event that a primary key is not enough to distinguish an object, a secondary key can be used to render that object unique.
  • Attributes used for Secondary Key are not the ones used for Super key i.e secondary Key is not even be one of the Super key.
  • Examples of secondary keys include: Street address number, Phone number, Middle name etc

Difference Between Primary Key And Secondary Key In Tabular Form

BASIS OF COMPARISON PRIMARY KEY SECONDARY KEY  
Description The attribute that uniquely identifies a row or record in a relation is known as Primary key. A field or combination of fields that is basis for retrieval is known as secondary key (mainly used for finding details from large data).
Use     It uniquely identifies a record in the relational database table. It is used for identification of rows but not usually unique.
NULL Values It does not allow NULL values cannot be deleted from the parent table.   Allows NULL values.
Number Of Keys Only one primary key is allowed in a table in a table.   We can have multiple secondary key per table.  
Examples Examples of primary keys include: Unique last name, Social security number, Online username   Examples of secondary keys include: Street address number, Phone number, Middle name etc  
Deletion Cannot be deleted from the parent table. Can be deleted from the parent table.