Introduction to Alternate Key in SQL
The alternate key is a combination of one or more columns whose values are unique. A table consists of one or more Candidate keys, in which one will be Primary Key and rest of the keys, are called as Alternate keys. Alternate Key is not part of the primary key. There can be one or more alternate keys in a table and contain NULL values unless it is mentioned to be NOT NULL explicitly. The UNIQUE keyword is used to define the alternate key, which indicates the value must be unique. Simple words to say Candidate keys that are not selected as primary key are called Alternate key.
Syntax:
UNIQUE (col1,col2…n);
What are the Keys?
Keys that are helps us to uniquely identify rows in a table. They allow finding a relation between two tables. Keys are a combination of one or more columns in a table.
Why we Need Keys?
If you take a real time example, a table can have any number of records in it and rows or records could be duplicated. To find a particular unique row data we need a key. Here keys ensure to get the unique table record.
For example, if we are fetching a particular row from a table with huge data in it. The normal way is that we apply few conditions. But if there are duplicates present in the table we get wrong data while fetching the data. We need to give a lot of trails to gets the right data.
This can be avoided by using keys.
Importance of Keys
- Keys are very important in the relational database model.
- Keys are used to identify the relationship between tables and are used to identify the rows inside a table.
- Key can be a combination of a single column or a combination of multiple columns.
- Without keys, it could be difficult to define a particular row in a huge table.
How to use Alternate Key in SQL?
Let us take a table example, build the SQL query statement, and define the alternate key.
Here we want Student ID to be primary key and Roll No to be Alternate key. Below SQL statement is written:-
Declaration:
Create table Student_data(
Student_ID INT NOT NULL,
Roll_No INT NOT NULL,
First_Name VARCHAR (20),
Last_Name VARCHAR(20),
Email VARCHAR(25),
Class VARCHAR(10),
Marks INT,
PRIMARY KEY(Student_ID),
UNIQUE(Roll_No)
);
INSERT INTO Student_data(Student_ID, Roll_No, First_Name, Last_Name, Email, Class, Marks)values
(11, 1, 'Shanti', 'Tallori', 'abx@gmail.com', 'MSC', 78),
(12, 2, 'Preeti', 'Nayak', 'pny@gmail.com', 'BPC', 90),
(13, 3, 'Arjun', 'Reddy', 'ard@gmail.com', 'BSC', 67),
(14, 4, 'Prakash', 'Rajan', 'xyz@gmail.com', 'MPC', 88);
select * from Student_data;
Example #1
Below is the table, which consists of data regarding the students. Here we have different keys as mentioned below-
Here in the above example Student ID, Roll No, and Email can be primary keys. If DBA selects Student ID as the primary key. Roll No and Email will become the Alternate or secondary key.
Example #2
Here in the above example Empid, Emp_aadhar are the candidate_keys, in which Empid is selected as primary Key and Emp_aadhar became the Alternate key.
Here Deptid is also unique but it is not considered as a primary key or alternate key because it is derived from another table called “Department” Table. If a column is derived from another table, then it is considered as the foreign key. The Empid acting as the primary key in the “Employee” table will act as the Foreign key in the “Department” table.
Declaration:
Create table Employee_table(
Empid INT NOT NULL,
Empname VARCHAR (20),
Aadhar INT NOT NULL,
Address VARCHAR(20),
Emp_sal INT NOT NULL,
Deptid INT , ------------------------------ Foreign Key------------------
Primary_Key(Empid), -------------- Primary Key Declartion-------------
UNIQUE(Emp_aadhar) ---------------- Alternate Key Declaration--------
)
Foreign key
- Foreign Key comes from another table.
- A Foreign key acts as a primary key in another table and is used to match rows in between two tables.
- In the above example, Deptid will be the primary key in the Department table but the Deptid acts as the foreign key in this employee table.
Definition of Candidate Key and Primary Key
- Candidate Key: Candidate Key are unique and can define the row of a table. Candidate keys of a table are eligible to be a primary key. It is a combination of two or more columns.
- Primary Key: Primary keys are the keys that uniquely define the row/ tuple of a table. Basically tables are joined based on Primary keys.
Rules for Alternate Key
Things to put in mind are:
- The alternate key is a part of a candidate key but not a part of the primary key.
- Defined by “UNIQUE”.
- Alternate keys values should be unique.
- If a table consists of only one candidate key, then it becomes the primary key; there will be no alternate key.
Recommended Articles
This is a guide to Alternate Key in SQL. Here we also discuss the Introduction and how to use the alternate key in SQL along with different examples and its code implementation. You may also have a look at the following articles to learn more –
