![SQL Logo](https://www.tutorialspoint.com/sql/images/sql-mini-logo.jpg)
- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Database
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 −
![Alternate](https://www.tutorialspoint.com/sql-new/images/alternate.jpg)
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 −
![Alternate](https://www.tutorialspoint.com/sql-new/images/alternate1.jpg)
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.
![Foreign Key](https://www.tutorialspoint.com/sql-new/images/foreign1.jpg)
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.