PRIMARY KEY
A primary key is a field that identifies each record in a database table admitting that the primary key must contain its UNIQUE values.
The primary key column cannot have NULL values.
A table can have only one primary key constraint which may consist of single and multiple fields.
When multiple keys are used in a single primary key, these fields are called a composite key.
When we specify the primary key constraint for a table, the database engine executes the data uniqueness by automatically creating an index. this index permits fast access to data when the primary key is used.
Primary Key can be specified either when the table is created using CREATE table or by changing the existing table structure using ALTER.
CREATE TABLE Customer (SID integer, Last_Name Varchar(20) First_Name Varchar(20) PRIMARY KEY (SID)
ALTER TABLE Customer ADD PRIMARY KEY (SID)
SECONDARY KEY
A secondary key shows the secondary value that is unique for each record. It can be used to identify the record and it is usually indexed. It is also termed as Alternate key.
A table may have a primary key that is system generated and a secondary key that comes from the sources and other processes. that is the secondary key. it is made on a field to be indexed for faster searches. A table can have more than one secondary key.
Difference between Primary key and Secondary key ?
The main difference between primary key and secondary key is, a key that is selected for identifying each tuple in a table uniquely is termed as primary key, whereas, a key that is not selected for identifying rows, even though it is capable of determining tuples uniquely in the table are termed as the secondary key.
| Basis | Primary key | Secondary key / Alternate key |
|---|---|---|
| Definition | A key that is selected for identifying each record in a database table uniquely | A key that is not selected as primary key but capable of identifying each records uniquely |
| NULL Values | Not Allowed | Allowed |
| Number of Keys | A table can have only one primary key | A table can have any number of secondary or alternate keys |
For Example ,
In the above table, we can identify candidate keys which are :
user_id, account_number and email
Now from them, we can select only one primary key, in this case, we selected user_id as the primary key. Other remaining keys such as account_number and email as Secondary or Alternate key.
Summary :
A Candidate Key can be defined as an individual column or combination of columns that are used to uniquely identify rows in a table. Both the Primary Key and Secondary key are Candidate Keys. They are capable of determining each and every tuple.
Frequently Asked Questions:
What is an alternate key ?
An alternate key can be defined as the candidate key that satisfies all the requirements to become a primary key. It’s also termed as secondary key
Is alternate key same as secondary key?
Yes, both are used interchangeably.
