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.
Join MindStick Community
You need to log in or register to vote on answers or questions.
We use cookies to ensure you have the best browsing experience on our website. By using our site, you
acknowledge that you have read and understood our
Cookie Policy &
Privacy Policy.
Let’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.