12

What difference between Super and Candidate key in ERDB?

M--
  • 25,431
  • 8
  • 61
  • 93
Stan Kurilin
  • 15,614
  • 21
  • 81
  • 132
  • 4
    Superkey and Candidate Key are relational database model terms. What does "ERDB" mean? Maybe you are referring to the Entity Relationship (ER) model which is a set of conventions for drawing pictures about data. The ER model is something different from the relational model. – nvogel Jun 21 '10 at 19:38

5 Answers5

22

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.

Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177
  • "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 Oct 26 '10 at 14:11
13

candidate key is a minimal superkey

Stan Kurilin
  • 15,614
  • 21
  • 81
  • 132
  • 5
    +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. – orip Jun 21 '10 at 20:14
  • 1
    Minimal in the sense that if one attribute from the candidate key is removed, the row cannot be uniquely identified anymore. – libjup Aug 13 '16 at 08:02
4
  • 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

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
gbn
  • 422,506
  • 82
  • 585
  • 676
  • A candidate key is a minimal superkey. So a superkey isn't strictly "wider" because a candidate key is a superkey too. – nvogel Jun 21 '10 at 19:41
  • @david: isn't "minimal superkey" an oxymoron? – gbn Jun 22 '10 at 04:40
  • "generally introduce ambiguities" -- what do you mean? For example, in this answer (http://stackoverflow.com/questions/3938736/enforce-constraints-between-tables/3940708#3940708) 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? – onedaywhen Oct 26 '10 at 14:18
  • 1
    @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? – gbn Oct 26 '10 at 15:32
  • @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 Oct 27 '10 at 07:17
  • @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. – onedaywhen Oct 27 '10 at 07:20
  • @onedaywhen: We'll have to disagree then. – gbn Oct 27 '10 at 07:26
  • @gbn: If you insist :) but in parting note this is a common enough technique e.g. see this article (http://www.simple-talk.com/sql/t-sql-programming/unique-experiences!/), section captioned, "Overlapping Uniqueness Constraints". – onedaywhen Oct 27 '10 at 07:36
  • @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. – gbn Oct 27 '10 at 07:48
  • @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 Oct 27 '10 at 07:58
  • 1
    @gbn: FWIW I myself believe in using natural keys but I'm dead against supernatural keys ;) – onedaywhen Oct 27 '10 at 08:01
  • You don't define "minimal" & these are circular. – philipxy May 21 '19 at 01:15
2

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.

Varun Garg
  • 565
  • 7
  • 12
0

In nutshell: CANDIDATE KEY is a minimal SUPER KEY.

Where Super key is the combination of columns(or attributes) that uniquely identify any record(or tuple) in a relation(table) in RDBMS.

For instance, consider the following dependencies in a table having columns A, B, C and D (Giving this table just for a quick example so not covering all dependencies that R could have).

Attribute set (Determinant)---Can Identify--->(Dependent)

A-----> AD

B-----> ABCD

C-----> CD

AC----->ACD

AB----->ABCD

ABC----->ABCD

BCD----->ABCD

Now, B, AB, ABC, BCD identifies all columns so those four qualify for the super key.

But, B⊂AB; B⊂ABC; B⊂BCD hence AB, ABC, and BCD disqualified for CANDIDATE KEY as their subsets could identify the relation, so they aren't minimal and hence only B is the candidate key, not the others.

Kartik Gautam
  • 108
  • 7