What is the definition of a secondary key?




You may have heard the term secondary key in Oracle, MySQL, SQL Server, or whatever other dbms you are dealing with. What exactly is a secondary key? Let’s start with a definition, and then a simple example that will help you understand further.

A given table may have more than just one choice for a primary key. Basically, there may be another column (or combination of columns for a multi-column primary key) that qualify as primary keys. Any combination of column(s) that may qualify to be a primary key are known as candidate keys. This is because they are considered candidates for the primary key. And the options that are not selected to be the primary key are known as secondary keys.

Example of a Secondary Key in SQL




Let’s go through an example of a secondary key. Consider a table called Managers that stores all of the managers in a company. Each manager has a unique Manager ID Number, a physical address, and an email address. Let’s say that the Manager ID is chosen to be the primary key of the Managers table. Both the physical address and email address could have been selected as the primary key, because they are both unique fields for every manager row in the Managers table. But, because the email address and physical address were not selected as the primary key, they are considered to be secondary keys.

Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

Subscribe to our newsletter for more free interview questions.

  • pc

    GIven that a SQL flavor does not have “secondary key” option then would constraints be a viable alternative?

  • surbhijain93

    Whats the diff b/w alternate and secondary key?

  • Umair Butt

    Great explanation …

  • swapnil p

    Yes, they are candidate keys, but as the author described that secondary keys are the candidates which ultimately don’t end up as a primary key. u can say that they r subset of candidate keys..

  • Morraity

    Are not the physical address and email address condidate as well,
    then what is the diffrence between Candidate and Secondary Keys

  • test

    nice explanation