What difference between Super and Candidate key in ERDB?
Thanks.
+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
2010-06-21 20:14:00
+2
A:
- 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
gbn
2010-06-21 19:13:28
A candidate key is a minimal superkey. So a superkey isn't strictly "wider" because a candidate key is a superkey too.
dportas
2010-06-21 19:41:48
"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
2010-10-26 14:18:03
@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
2010-10-26 15:32:56
@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
2010-10-27 07:17:37
@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
2010-10-27 07:20:15
@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
2010-10-27 07:36:16
@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
2010-10-27 07:48:12
@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
2010-10-27 07:58:55
@gbn: FWIW I myself believe in using natural keys but I'm dead against supernatural keys ;)
onedaywhen
2010-10-27 08:01:34
+3
A:
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
2010-06-21 19:14:20
"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
2010-10-26 14:11:51