SQL - Alternate key

---

An Alternate Keys of a table are those candidate keys that are not currently selected as the primary key of a table. These are the secondary candidate keys that can be used to uniquely identify a tuple(or a record) in a table.

There is no specific query or syntax to set the alternate key in a table. It is just a column that is a close second candidate which could be selected as a primary key.

A KEY plays an important role in a relational database. It is an attribute or set of attributes, that helps us to identify the unique row(or record) in a table. It also establishes the relationship among tables.

Note − If a table consists of only one Candidate key that is treated as the primary key of the table, then there is no alternate key in that table.

Let us suppose we have a table named CUSTOMERS with various fields like ID, Name, Mobile, address, etc. In this table ID and Mobile are treated as candidate keys. By using these two fields we can get the unique record from the Customer's table. Among them, one is treated as the primary key and another key is known as the alternate key of the Customer's table.

Let’s understand with a proper table diagram −

In the above customer's table, columns ID and Mobile_no are known as candidate keys. So if you consider the column “ID” as a primary key, then the column “Mobile_no” is known as the alternate key of the customer's table.

Even though alternate keys are not primary keys, they contain some important properties/features of their own. They are listed below.

• The alternate key does not allow duplicate values.
• A table can have more than one alternate keys.
• The alternate key can contain NULL values unless the NOT NULL constraint is set explicitly.
• All alternate keys can be candidate keys, but all candidate keys can not be alternate keys. As a primary key, which is also a candidate key, can not be considered as an alternate key.

Keys in a table

Below is the list of keys that are present in a table −

• Candidate key
• Primary key
• Alternate key
• Foreign Key

Candidate Key

A Candidate key is a subset of super keys that is used to uniquely identify records of a table. It can be a single field or multiple fields. The primary keys, alternate keys, foreign keys in a table are all types of candidate key.

Primary Key

A Primary Key is a main key that is used to retrieve records from a table. It is a single column or field in a table that uniquely identifies each record in a database table.

It can be set using the PRIMARY KEY keyword while creating a table using the CREATE TABLE statement.

Syntax

CREATE TABLE COLUMN_NAME1, COLUMN_NAME2… PRIMARY KEY(COLUMN_NAME));

Alternate Key

An Alternate key is a Candidate key that could be a primary key but is not. Like primary key, it also uniquely identifies the records in a field of a table to retrieve row tuples from the said table. There can be a single or multiple fields identifying as alternate keys in a table.

There is no syntax to set an alternate key in a database table.

Foreign Key

The Primary key of one table will be the Foreign key in another table. While inserting values into these tables, values in the primary key field must match the values in the foreign key field; otherwise, the foreign key column will not accept the INSERT query and throws an error.

In SQL server, the syntax to set a foreign key field in a table is −

CREATE TABLE TABLE_2(COLUMN_NAME FOREIGN KEY REFERENCES TABLE_1(COLUMN_NAME));

Let us see an example demonstrating the usage of the given keys and illustrating the fields that can be considered as an alternate key in tables created.

Example

In the following example, we are creating a table name CUSTOMERS with various fields in the SQL database shown below the SQL statement −

CREATE TABLE CUSTOMERS(
   ID INT,
   NAME VARCHAR (20),
   AGE INT,
   ADHARCARD_ID BIGINT,
   MOBILE_NO BIGINT,
   ADDRESS CHAR (25) ,
   SALARY DECIMAL (18, 2),
   PRIMARY KEY(ID)      
);

Now let’s insert some records into the Customer's table using the INSERT statement as shown below −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADHARCARD_ID,MOBILE_NO, ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 901234984567, 9021345687,'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADHARCARD_ID,MOBILE_NO,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 911232495457,9021345687, 'Delhi', 1500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADHARCARD_ID,MOBILE_NO,ADDRESS,SALARY)
VALUES (3, 'Kaushik', 23, 912232425416, 9824434518, 'Kota', 2000.00);

Now let’s display the table data using the SELECT statement as shown below

SELECT * FROM CUSTOMERS;

The table will be displayed as given below −

+----+---------+------+--------------+------------+-----------+---------+
| ID | NAME    | AGE  | ADHARCARD_ID | MOBILE_NO  | ADDRESS   | SALARY  |
+----+---------+------+--------------+------------+-----------+---------+
|  1 | Ramesh  |   32 | 901234984567 | 9021345687 | Ahmedabad | 2000.00 |
|  2 | Khilan  |   25 | 911232495457 | 9021345687 | Delhi     | 1500.00 |
|  3 | Kaushik |   23 | 912232425416 | 9824434518 | Kota      | 2000.00 |
+----+---------+------+--------------+------------+-----------+---------+
3 rows in set (0.00 sec)

In the above table, the keys are set as follows −

But to display the Foreign key, we would need two tables. Following is the query to create another table ORDERS with the foreign key set as CUSTOMER_ID.

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE DATETIME, 
   CUSTOMER_ID INT FOREIGN KEY REFERENCES CUSTOMERS(ID),
   AMOUNT DECIMAL,
   PRIMARY KEY (ID)
);

Using the INSERT statement, insert values into this table as follows −

INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (102, '2009-10-08 00:00:00', 3, 3000.00);
INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (101, '2009-11-20 00:00:00', 2, 1560.00);
INSERT INTO ORDERS (OID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (100, '2009-10-08 00:00:00', 1, 1500.00);

To display the ORDERS table, we can use the following query −

SELECT * FROM ORDERS;

The table will be displayed as follows −

+-----+---------------------+-------------+---------+
| OID | DATE                | CUSTOMER_ID | AMOUNT  |
+-----+---------------------+-------------+---------+
| 100 | 2009-10-08 00:00:00 |           1 | 1500.00 |
| 101 | 2009-11-20 00:00:00 |           2 | 1560.00 |
| 102 | 2009-10-08 00:00:00 |           3 | 3000.00 |
+-----+---------------------+-------------+---------+

The foreign key will be referenced to the ID column in the Customers table above.

We can see that the values in Foreign key field of ORDERS table will match the values in primary key field of CUSTOMERS table.

Rules to be followed for alternate keys

Below are list of rules of alternate keys that should be followed while inserting the record into a table −

• Alternate key values should be unique.
• Alternate key can not be NULL.