The questions about secondary key in SQL is common in most database interviews. The Candidate keys which are not selected as primary key is classified as Secondary key. Consider that you have a table, there will be several keys in a table which can be selected as a primary key. The keys which has the qualities to be selected as primary are called as candidate keys. Only one candidate key can be selected as a primary key, the rest of the candidate keys can be classified into secondary keys. The secondary keys are also known as alternate keys.

• Learn: Aptitude Q&A

Example for secondary key

Consider a university table which stores the details of all colleges under its governance. Each college will have an unique college id, website and address; one of these unique fields can be selected as primary key. If the college id is chosen as a primary key, the keys like website and address can be defined as secondary or alternate keys.

Why secondary keys are required?

If a surrogate primary key is chosen instead of natural primary key, then secondary keys will play an important role in your database. Not using an alternate key while using surrogate key is a common mistake among many novice database programmers. Surrogate key is used to uniquely identify a row in table but it has no relation to the table. The declaration of surrogate key as a primary key can be confirmed using identity property.

Using identity property in SQL will auto increment a column when a row is added. This process increases the flexibility of database which may require changes in near future. If natural primary key is used instead of surrogate keys, the change of primary key may be required. Changing primary key in a database is a complicated process. One problem with the use of surrogate key is data duplication. To prevent data duplication in a database a secondary key can be used.

• Recommended: Final year Academic projects download

What if a table was created without secondary key?

Let us consider a table was created and a surrogate key was chosen as a primary key. If data duplication occurs because the developer may have forgot to use a secondary key. The data duplication can be removed by identifying bad rows which comprises duplicated data. The duplicated data can be searched and removed manually or by using sp_DataProfile. After removing the duplicated data, secondary keys can be declared which will prevent further data duplication.

• Related: SQL & .NET Interview questions and Answers

Difference between Primary and Secondary Keys

• Only one primary key is allowed in a table while number of secondary keys used in a table is not restricted.
• Using surrogate primary key may lead to data duplication whereas using a secondary along with surrogate primary key can prevent data duplication.
• Both primary and secondary keys cannot be declared as NULL