Super key in database can be defined as the combination of fields which can be used to uniquely identify a row present in a table. A unique super key is used for each and every row present in a RDBMS table. At least one field in a superkey must have value while the rest can be declared as NULL. Superkeys are used when a single attribute fails to uniquely identify a row in a table.
Super key – Example
Let us consider a table which contains a list of colleges governed by a university. The table may contain fields like college id, college name, department name and department id. Each field will have an unique value in rows. If the college id field is combined with the college name, a new set of values is produced. Similarly fields like (college id, department name) and (college id, department id) are also combined. The data present in the rows will not be similar because all the fields used in the combination are unique. The college id used in the table can be established as primary key. Any combination of primary key along with another field is called as super key. At least one super key is required to create a table in relational database management system.
- Read: What is Natural Key ?
What is Minimal Super key?
Minimal super key can be defined as the minimum number of columns required to define super key. Usually when a secondary key is combined with primary key, it will result in minimal super key. A field used in super key can be null so according to our example college id can be declared as minimal super key.
Do Multiple Super keys are allowed in a table?
Multiple super keys are allowed in a table. In our example, both college id and college name are unique which can be used as a super key when combined with other fields. The data present in college id and college name columns will be unique throughout the table.
Do multiple columns are allowed in minimal super key?
If there is no single column which can result in a unique value for each row, a combination of columns can be used to result the required unique value. However the combination of the columns should be in lowest possible value to declare itself as a minimal super key.
Why it is called as a super key?
According to the Relational Database Management System (RDBMS) theory, a superkey is a super set of subsets. Superkey is a combination of keys which can identify a row in a table.
Difference between Candidate and Super keys
- Superkeys can contain two or more fields but only one field is allowed in candidate keys
- All candidate keys can be declared as primary keys.
- Minimal super key without redundant attribute can be declared as Candidate keys.
- All candidate keys except primary key can be classified into secondary keys.
- Minimal superkey with no redundant attribute can be classified as secondary keys.
- Super keys are super set of two or more keys while candidate key is a minimal super key which has no subset.
- A minimal super key with single attribute is chosen by the developer to declare it as primary key.
- Some superkey with more than one attribute can be declared as composite key.
- Candidate keys which are not chosen as primary key are called as alternate keys.
- Only minimal superkeys are classified into alternate keys.
- Every candidate key is a super key. Not every superkey is a candidate key
- Minimal superkey which declared as primary is notated by an underline.
- The most irreducible superkey is declared as a candidate key
- The attributes used in candidate key cannot be declared as NULL while at least one attribute of superkey has a value.