SQL – ALTERNATE KEY

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 ID	Product Name	Price
1001	Washing Soap	25
1020	Shampoo	150
1030	Notebook	200
1045	Headphone	1000

Customer Information
Customer ID	Customer Name	Email Address	Shipping Address	Pan Number	Product ID
1	Madhulika	abc@gmail.com	XYZ-Colony, Patna	XXABX10011	1030
2	Tanmoy	tdq@gmail.com	ABC-Colony, Guwahati	DDABX10034	1001
3	Ritik	def@gmail.com	XYZ_Street, Chennai	ACQBX10555	1045
4	Satadru	sm11@gmail.com	Park_Street, Kolkata	ZZABX20035	1045

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 whether an integer, variable character, etc
col_constraint: Constraints in SQL like PRIMARY KEY, NOT NULL, UNIQUE, REFERENCES, etc.
col_3: Defining an ALTERNATE KEY using constraint UNIQUE
col_4: Defining 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