Superkey vs. Candidate key

Question

What difference between Super and Candidate key in ERDB?

Solution 1

candidate key is a minimal superkey

Solution 2

A superkey is a set of columns that uniquely identifies a row. A Candidate key would be a MINIMAL set of columns that uniquely identifies a row. So essentially a Superkey is a Candidate key with extra unnecessary columns in it.

Solution 3

• Candidate key = minimal key to identify a row
• Super key = at least as wide as a candidate key

For me, a super key would generally introduce ambiguities over a candidate key

Solution 4

Let's keep it simple

SuperKey - A set of keys that uniquely defines a row.So out of all the attributes if even any single one is unique then all the subsets having that unique attribute falls under superkey.

Candidate Key - A superkey out of which no further subset can be derived which can identify the rows uniquely, Or we can simply say that it is the minimal superkey.

View more solutions

Comments

[nvogel]

A candidate key is a minimal superkey. So a superkey isn't strictly "wider" because a candidate key is a superkey too.

[orip]

+1, plus it would be good to define "minimal" here since it's non-intuitive. For example in table (a,b,c), with (a) being unique and also (b,c) being unique, both (a) and (b,c) should be candidate keys.

[ZygD]

@david: isn't "minimal superkey" an oxymoron?

[onedaywhen]

"a Superkey is a Candidate key with extra unnecessary columns" -- the extra columns may actually be necessary to reference the table and ensure data integrity e.g. (employee_ID) is unique but a key on (employee_ID, department_name) may be required for certain tables that restrict employees according to their department.

[onedaywhen]

"generally introduce ambiguities" -- what do you mean? For example, in this answer (stackoverflow.com/questions/3938736/…) the EmployeeDepartments table has a candidate key (employee_ID) and a superkey (employee_department_name, employee_ID). Both are required for data integrity, where's the ambiguity?

[ZygD]

@onedaywhen: (employee_department_name, employee_ID) means you can have the same employee_ID for multiple employee_department_name values. How can employee_ID itself be a unique key then?

[onedaywhen]

@gbn: (employee_department_name, employee_ID) is a key (a superkey) because (employee_ID) is in itself a key (a candidate key), so no it is not possible to have the same employee_ID for multiple employee_department_name values.

[onedaywhen]

@gbn: ...employee_department_name is "brought in" to the key so that it can be referenced (via a foreign key) from another table. So my point is that a superkey does have a practical use in data integrity constraints.

[ZygD]

@onedaywhen: We'll have to disagree then.

[onedaywhen]

@gbn: If you insist :) but in parting note this is a common enough technique e.g. see this article (simple-talk.com/sql/t-sql-programming/unique-experiences!), section captioned, "Overlapping Uniqueness Constraints".

[ZygD]

@onedaywhen: I have no objection to multiple and/or overlapping unique keys. I do object to the term "superkey" when plainly (employee_department_name, employee_ID) does not uniquely identify a row if (employee_ID) is already the primary key. It is neither a candidate nor super key, even if it is defined unique for use by an FK.

[onedaywhen]

@gbn: so we agree it fits the definition of "superkey", it's just that you have a problem with the whole concept of superkeys? I'm not here to change you belief system, peace :)

[onedaywhen]

@gbn: FWIW I myself believe in using natural keys but I'm dead against supernatural keys ;)

[libjup]

Minimal in the sense that if one attribute from the candidate key is removed, the row cannot be uniquely identified anymore.

[philipxy]

You don't define "minimal" & these are circular.