Provide a definition and example of a superkey in SQL.
In SQL, the definition of a superkey is a set of columns in a table for which there are no two rows that will share the same combination of values. So, the superkey is unique for each and every row in the table. A superkey can also be just a single column.
Example of a superkey
Suppose we have a table that holds all the managers in a company, and that table is called Managers. The table has columns called ManagerID, Name, Title, and DepartmentID. Every manager has his/her own ManagerID, so that value is always unique in each and every row.
This means that if we combine the ManagerID column value for any given row with any other column value, then we will have a unique set of values. So, for the combinations of (ManagerID, Name), (ManagerID, TItle), (ManagerID, DepartmentID), (ManagerID, Name, DepartmentID), etc – there will be no two rows in the table that share the exact same combination of values, because the ManagerID will always be unique and different for each row. This means that pairing the Manager ID with any other column(s) will ensure that the combination will also be unique across all rows in the table.
And that is exactly what defines a superkey – it’s any combination of column(s) for which that combination of values will be unique across all rows in a table. So, all of those combinations of columns in the Manager table that we gave earlier would be considered to be superkeys. Even the ManagerID column is considered to be a superkey, although a special type of superkey as you can read more about below.
What is a minimal superkey?
A minimal superkey is the minimum number of columns that can be used to uniquely identify a single row. In other words, the minimum number of columns, which when combined, will give a unique value for every row in the table. Remember that we mentioned earlier that a superkey can be just a single column. So, in our example above, the minimal superkey would be the ManagerID since it is unique for each and every row in the Manager table.
Can a table have multiple minimal superkeys?
Yes, a table can have multiple minimal superkeys. Let use our example of a Manager table again. Suppose we add another column for the Social Security Number (which, for our non-American readers, is a unique 9 digit number assigned to every citizen of the USA) to the Manager table – let’s just call it SSN. Since that column will clearly have a unique value for every row in the table, it will also be a minimal superkey – because it’s only one column and it also is unique for every row.
Can a minimal superkey have more than one column?
Absolutely. If there is no single column that is unique for every row in a given table, but there is a combination of columns that produce a unique value for every row in a table, then that combination of columns would be the minimal superkey. This is of course provided that the combination is the smallest number of columns necessary to produce a unique value for each row.
Why is it called a superkey?
It’s called a superkey because it comes from RDBMS theory, as in superset and subset. So, a superkey is essentially all the superset combinations of keys, which will of course uniquely identify a row in a table.
Superkey versus candidate key
We discussed minimal superkeys and defined exactly what they are. Candidate keys are actually minimal superkeys – so both candidate keys and minimal superkeys mean exactly the same thing.