MySQL - Alternate Key

---

---

An Alternate Key in a table is nothing but an alternative to the primary key in that table. In other words, they are candidate keys that are not currently selected as the primary key of a table (but has a potential to be one). Hence, they can also be used to uniquely identify a tuple(or a record) in a table.

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.

There is no specific query or syntax to set the alternate key in a table. It is just an ordinary column that is a secondary candidate to be selected as a primary key.

Features of Alternate Keys

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.

Types of 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 either be a single field or multiple fields. 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. The syntax is as follows −

CREATE TABLE table_name(
   COLUMN_NAME1 datatype, 
   COLUMN_NAME2 datatype,
   ... 
   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.

The syntax to set a foreign key field in a table is −

CREATE TABLE table_name2(
   ... CONSTRAINT constraint_name 
   FOREIGN KEY (column_name2) 
   REFERENCES table_name1(column_name1)
);

Example

In the following example, we are creating a sample table named CUSTOMERS in the MySQL database −

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

Now let us insert some records into this table created using the INSERT statement as shown below −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

Verification

To verify the keys added to the CUSTOMERS table, let us display the table definition using the following query −

Field	Type	Null	Key	Default	Extra
ID	int	NO	PRI	NULL
NAME	varchar(20)	NO		NULL
AGE	int	NO		NULL
ADDRESS	char(25)	YES		NULL
SALARY	decimal(18, 2)	YES		NULL

Currently, only PRIMARY KEY is set in the table on ID column. The NAME column acts like the Alternate Key, as it will only contain unique records like a Primary Key column. Whereas, both ID and NAME are the Candidate Keys in the CUSTOMERS table.

Example

To illustrate the usage of 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 VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2),
   CONSTRAINT fk_customers FOREIGN KEY (CUSTOMER_ID)
   REFERENCES CUSTOMERS(ID)
);

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

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Verification

To verify if the ORDERS table is referenced to the CUSTOMERS table or not, we drop the CUSTOMERS table without dropping the ORDERS table.

DROP TABLE CUSTOMERS;

Following error is displayed −

ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'fk_customers' on table 'orders'.

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.