Skip to content
Related Articles

Related Articles

Improve Article
SQL – ALTERNATE KEY
  • Difficulty Level : Medium
  • Last Updated : 14 May, 2021

Keys are an important part of any Relational Database. There are various types of keys and among one of these is the Alternate Key. The keys that contain all the properties needed to become a Candidate Key are known as Alternate Keys. These are basically secondary Candidate Keys that can uniquely identify a row in a table. So, Alternate Keys are also sometimes known as “Secondary Keys”.

In other words, we can define the Alternate key as the set of Candidate Keys other than the Primary Key. There can be many Candidate Keys for a given table and out of all these the Database Administrators selects only one of these as the Primary Key. Hence, the other Candidate Keys which are not used as a Primary Key are the “Alternate Keys”. 

Some important points about Alternate Keys are as follows :

  1. A Primary Key can’t be an Alternate Key. For a table with a single Candidate Key which has to be the Primary Key will not contain any Alternate Key.
  2. A Foreign Key can’t be an Alternate Key as it is only used to reference another table.
  3. The alternate Key should be unique.
  4. An Alternate Key can be a set of a single attribute or multiple attributes.
  5. It can be NULL as well. 

In this article, we are going to see how to create an ALTERNATE Key in SQL using sample tables as shown.

Sample Input : Consider the Table Customer Information which consists data about customers who bought products from an E-Commerce site. This table is referencing the Product Information table to know about the details of the product bought by a customer. The common attribute used for referencing is “Product ID” which is also termed as Foreign Key.



         Product Information 
Product IDProduct NamePrice
1001Washing Soap25
1020Shampoo150
1030Notebook200
1045Headphone1000
                                                                  Customer Information
Customer IDCustomer NameEmail AddressShipping AddressPan NumberProduct ID
1Madhulikaabc@gmail.comXYZ-Colony, PatnaXXABX100111030
2Tanmoytdq@gmail.comABC-Colony, GuwahatiDDABX100341001
3Ritikdef@gmail.comXYZ_Street, ChennaiACQBX105551045
4Satadrusm11@gmail.comPark_Street, KolkataZZABX200351045

In the Customer Information Table, Customer ID, Pan Number, Email Address are unique as it can uniquely identify a row in the given table. PAN Number is unique for every person and Customer ID is also a unique number provided by E-Commerce sites to distinguish among tons of customers registered in their shopping site. 

A user can register on the shopping site using only a single E-Mail Address. If he/she wants to create another account using the same E-Mail will show a message, “An account with this E-Mail Address already exists, Please Login”. So, every consumer will have a unique E-Mail Address. Hence, all these attributes can uniquely identify a row in a table.

The candidate key set for the above table is : { Customer ID, Pan Number, Email Address }

Say, the Data Base Administrator of this E-Commerce site picked Customer ID as the Primary Key. Therefore, PAN Number and E-Mail Address will be Alternate Keys or Secondary Keys. Alternate Key has all the properties to become a Primary Key and so is an alternate option.

ALTERNATE Keys in SQL are defined using the SQL constraint UNIQUE.

UNIQUE(col_name(s))

col_name(s): The name of the column(s) in the table which need to be unique.

BASIC SQL QUERY :

1. Creating a Database

CREATE DATABASE database_name

2. Creating a Table

CREATE TABLE Table_name(
col_1 TYPE col_1_constraint,
col_2 TYPE col_2 constraint,
col_3 TYPE UNIQUE,
col_4 TYPE REFERENCES Table_Name(col_name),
.....
)

col: The name of the columns.
TYPE: Data type wheher an integer, variable character, etc
col_constraint: Constraints in SQL like PRIMARY KEY, NOT NULL, UNIQUE, REFERENCES, etc.
col_3: Definining an ALTERNATE KEY using constraint UNIQUE
col_4: Definining an FOREIGN KEY using constraint REFERENCES

3. Inserting into a Table

INSERT INTO Table_name
VALUES(val_1, val_2, val_3, ..........)

val: Values in particular column

4. View The Table

SELECT * FROM Table_name

Output :

Product Table

Customer Table

A pictorial view of all the keys present in the table is shown below :

KEYS

My Personal Notes arrow_drop_up
Recommended Articles
Page :