Let’s take an example of a Student table and understand the concept of keys in the database in more clear way:
CREATETABLEStudent ( StudentID, StudentName, DOB, -- Date of birth Subjects PMN, -- Personal mobile Number DeptID, -- Department ID class )
1. Candidate Key: is the attribute/column or a set of attributes/columns in a relation/table that qualifies for uniqueness of each tuple/row. A relation/table can have one or more than one Candidate Keys. A Candidate key is also known as a minimal Super key.
Here in Student table columns StudentID & PMN individually can maintain uniqueness in a table, thus are eligible for Candidate keys. The columns StudentName + DOB combined can also make up a Candidate Key, but there is a narrow chance that 2 Students with same name can be born in same day.
2. Primary Key: is the Candidate key attribute/column that is most suited to maintain uniqueness in a table at the tuple/row level.
Here in Student table you can choose either StudentID or PMN column for a Primary key, StudentID is preferable choice because PMN is a secure (PII) value contains privacy.
3. Alternate Key: are the other Candidate key attribute/columns that you didn’t choose as Primary key column.
Like if you choose StudentID as a Primary key then PMN would be the Alternate key.
4. Super Key: It is a superset of Candidate key. If you add any other attribute/column to a Candidate Key then it became a Super Key.
Like StudentID + StudentName is a Super Key.
5. Composite Key: If a table do have a single column that qualifies for a Candidate key, then you have to select 2 or more columns to make a row unique.
Like if there is no StudentID or PMN columns in Student table, then you can make StudentName + DOB as a Composite Primary Key. But still there can be a narrow chance of duplicate rows.
Liked By
Write Answer
Elaborate all type of keys in sql?
Join MindStick Community
You have need login or register for voting of answers or question.
Sunil Singh
11-Jul-2017Let’s take an example of a Student table and understand the concept of keys in the database in more clear way:
1. Candidate Key: is the attribute/column or a set of attributes/columns in a relation/table that qualifies for uniqueness of each tuple/row. A relation/table can have one or more than one Candidate Keys. A Candidate key is also known as a minimal Super key.
Here in Student table columns StudentID & PMN individually can maintain uniqueness in a table, thus are eligible for Candidate keys. The columns StudentName + DOB combined can also make up a Candidate Key, but there is a narrow chance that 2 Students with same name can be born in same day.
2. Primary Key: is the Candidate key attribute/column that is most suited to maintain uniqueness in a table at the tuple/row level.
Here in Student table you can choose either StudentID or PMN column for a Primary key, StudentID is preferable choice because PMN is a secure (PII) value contains privacy.
3. Alternate Key: are the other Candidate key attribute/columns that you didn’t choose as Primary key column.
Like if you choose StudentID as a Primary key then PMN would be the Alternate key.
4. Super Key: It is a superset of Candidate key. If you add any other attribute/column to a Candidate Key then it became a Super Key.
Like StudentID + StudentName is a Super Key.
5. Composite Key: If a table do have a single column that qualifies for a Candidate key, then you have to select 2 or more columns to make a row unique.
Like if there is no StudentID or PMN columns in Student table, then you can make StudentName + DOB as a Composite Primary Key. But still there can be a narrow chance of duplicate rows.