SQL - ALTERNATE KEY

Alternate Key is any candidate key not selected as the primary key. So, while a table may have multiple candidate keys (sets of columns that could uniquely identify rows), only one of them is designated as the Primary Key. The rest of these candidate keys become Alternate 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 Administrator selects only one as the Primary Key. Hence, the other Candidate Keys that 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.

Creating an Alternate Key in SQL

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

Consider two tables, Product Information and Customer Information. We will assume that we are creating a customer information system where we want to keep track of customers' orders. In this case, we use the Product ID as a Foreign Key in the Customer Information table to reference products purchased by customers.

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 :

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

Key Differences Between Primary Key and Alternate Key

Conclusion

Alternate Keys are an essential feature in SQL that helps maintain data uniqueness and integrity. While the Primary Key is the most important key used to uniquely identify rows in a table, Alternate Keys provide additional options for uniqueness constraints and can be used to enforce data integrity across multiple columns. Understanding how to use and define alternate keys can significantly improve database design, particularly in systems where multiple attributes could serve as a unique identifier for records.