11

I need to understand the difference between super key and composite key. The examples I found made more confused. Can you please simply clarify what is the difference? Thanks

Bob
  • 564
  • 2
  • 10
  • 25

4 Answers4

21

The accepted answer is not entirely accurate...

  • A superkey is any set of columns that, combined together, are unique. There are typically many superkeys per table and same column may be shared by many superkeys. They are not very useful by themselves, but are more of a mental tool for identifying candidate keys (see below).
  • A candidate key is a minimal superkey - if any column is removed it would no longer be unique. There are typically significantly fewer candidate keys than superkeys.
  • A key is just a synonym for a candidate key.
  • A composite1 key is a key that has more than one column. In other words, it's a minimal superkey that has multiple columns.

Few more points:

  • Every key is unique, so calling it "unique key" is redundant. Just "key" is enough.
  • At the DBMS level, a key is enforced through a PRIMARY KEY or UNIQUE2 constraint.
  • An index is usually present underneath the key (PRIMARY KEY or UNIQUE constraint), for performance reasons. But despite often going together, key and index are separate concepts: key is a logical concept (changes the meaning of data) and index is a physical concept (doesn't change the meaning of data, just performance).

1 Aka. compound, complex or concatenated.

2 On NOT NULL columns.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • An SQL KEY or UNIQUE constraint is actually a superkey constraint on some columns, since a proper subset of them could be constrained to be unique, in which case they don't form a key. – philipxy Jun 17 '14 at 06:38
  • I said: _"a key is enforced through a PRIMARY KEY or UNIQUE constraint"_. I didn't say: _"**only** a key is enforced through a PRIMARY KEY or UNIQUE constraint"_. In any case, such "Russian doll" constraints don't happen in a properly modeled database, except in some [special cases](http://stackoverflow.com/a/9178524/533120), justified by certain *incidental* deficiencies in modern database systems, rather than by *essential* need of the data model. – Branko Dimitrijevic Jun 17 '14 at 09:04
  • I just added something relevant that you didn't say. I didn't say you said something wrong. I think what is essential is a simple clear exact understanding of what an SQL FK constraint is. And it isn't a FK constraint, and it is a foreign superkey constraint. And if that's the constraint one happens to need, one can declare one. – philipxy Jun 17 '14 at 09:50
  • @philipxy I beg to differ. The point about constraining a proper superkey is irrelevant in most scenarios. The OP is clearly a novice in the field, and we should not push him/her to onto intricacies of implementing inheritance at this point. FKs are not keys and therefore not directly related to the question, and the same argument (as I outlined above) applies for them too anyway - in almost all practical situations, FK _will_ reference a key, not a proper superkey. – Branko Dimitrijevic Jun 17 '14 at 09:59
  • 1
    @philipxy _"It is a fact that an SQL FK declaration, like a UNIQUE declaration, declares a superkey."_ - no it doesn't. FK references a (super)key but is not one itself. – Branko Dimitrijevic Jun 17 '14 at 12:37
  • @philipxy FK is nether key nor superkey. – Branko Dimitrijevic Jun 17 '14 at 13:01
  • It is a fact that an SQL PRIMARY KEY or plain KEY declaration, like a UNIQUE declaration, declares a superkey, not a key. So tell people. Not telling them is misinforming them. No intricacies such as you have mentioned in two comments are germane. (Either to telling people or to arguing against telling people.) Agree that an SQL FK declaration declares a foreign superkey. Naturally, I think people should be told that. – philipxy Jun 17 '14 at 20:52
  • @philipxy And I agreed with it, and pointed it it's not really relevant in this context. Furthermore, you seem to agree it's not relevant: _"No intricacies such as you have mentioned in two comments are germane."_. So what exactly is the problem? – Branko Dimitrijevic Jun 18 '14 at 10:44
  • 1. I have confused by accidentally writing FK for (candidate) key in my 2nd comment. (But everything there is correct; it's also correct re PK/key.) (I had been editing elsewhere re FKs.) Also in another comment that I deleted & reposted corrected. (So you complained twice about now-gone typos.) 2. Relevant to defining/teaching superkey & candidate key is a correct definition of SQL PK/KEY [sic] declaration. (A similar semantic & pedagogical situation is SQL FK [sic] declaration.) Not relevant is modeling, special cases, deficiencies, scenarios, inheritance, situations etc. Just "K"=superkey. – philipxy Jun 19 '14 at 10:12
  • @philipxy Every key is superkey so PRIMARY KEY on a key is also a PRIMARY KEY on a superkey (ditto for UNIQUE). It is also possible to create a PRIMARY KEY (or UNIQUE) on a **proper** superkey, but is not very useful, except in few narrow scenarios, one of them being listed in my link above. And I'm still having a hard time understanding what exactly are you complaining about? Is there anything in the two starting sentences in this comment that you would disagree with? – Branko Dimitrijevic Jun 19 '14 at 10:59
  • My only points have been: 1. Added that an SQL PK/KEY declaration declares a superkey, not a CK per se. 2. Your objections to adding/teaching that are specious. (Because you a. object to teaching a simple relevant fact & b. claim wrongly that all sorts of complex things must be brought in to to teach it.) Yes, PK properly constrains a CK. But "not very useful" is specious. If PK is seldom used on or bad style to use on a non-CK teach that. At issue is an (unintended) lie by omission that PK declares a CK. Your focus is use, mine is meaning. My comments are clear & I repeat myself. – philipxy Jun 19 '14 at 12:27
  • @philipxy So you still refuse to respond on my clearly stated points? OK, I think I've spent enough time trying to understand your "arguments". You either have a problem with your command of the English language, of your command of the elementary logic, or both. Improve on these fronts and we may have a real conversation, but until that day comes, I'm done here. – Branko Dimitrijevic Jun 19 '14 at 14:04
  • One might add the term INDEX KEY to the list. INDEX KEY arguably doesn't belong here, because it's a physical concept rather than a logical one. Still I want to add it because it's another thing the beginner ought to learn. Like other kinds of keys, an index key can be either simple or composite. – Walter Mitty Aug 10 '18 at 19:51
  • What does "Composite key: minimal superkey that has multiple columns" mean? Can superkey select all the columns in a given table? If so, then why don't we always use `composite key`? Since it is minimal and has the superkey property? Or when do we use `superkey` or `composite key`? – Ahmet Nov 04 '20 at 18:24
  • @Ahx All columns together always form a superkey - otherwise the table would not be a set (mathematical "relation"), it would be multi-set. Some subsets of all columns may also be superkeys. Some of superkeys are keys (i.e. minimal). Some of keys may be composite (i.e. have more than one column). It's the keys (composite or otherwise) that we enforce through PRIMARY KEY and UNIQUE constraints (superkeys are just an intellectual tool). – Branko Dimitrijevic Nov 05 '20 at 09:03
11

Yes, I agree with @Branko, the accepted answer is not the accurate and not clear.

I'll take example of an Employee table:

CREATE TABLE Employee (
Employee ID, 
FullName, 
SSN, 
DeptID
);

And to know difference b/w Super & Candidate keys, let's check first what are other types of keys?

1. Candidate Key: are individual columns in a table that qualifies for uniqueness of all the rows. Here in Employee table EmployeeID & SSN are Candidate keys.

2. Primary Key: is the columns you choose to maintain uniqueness in a table. Here in Employee table you can choose either EmployeeID or SSN columns, EmployeeID is preferable choice, as SSN is a secure value.

3. Alternate Key: Candidate column other the Primary column, like if EmployeeID is PK then SSN would be the Alternate key.

4. Super Key: If you add any other column/attribute to a Primary Key then it become a super key, like EmployeeID + FullName is a Super Key.

5. Composite Key: If a table don't have any individual columns that qualifies for a Candidate key, then you have to select 2 or more columns to make a row unique. Like if there is no EmployeeID or SSN columns, then you can make FullName + DateOfBirth as Composite primary Key. But still there can be a narrow chance of duplicate row.

Reference

Nishant Baranwal
  • 1,048
  • 1
  • 10
  • 18
Manoj Pandey
  • 1,307
  • 12
  • 14
0

A superkey is a set of one or more attributes that, taken collectively, allow us to identify uniquely an entity in the entity set. For example, the customer-id attribute of the entity set customer is sufficient to distinguish one customer entity from another. Thus,customer-id is a superkey.

Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.

Vaibhav Singh
  • 152
  • 1
  • 10
-2

A super key uniquely identifies a row. It could be made up of one column or many. A composite key is a key made of more than one column.

If a Super Key is made of more than one column it is also a composite.

If a composite key uniquely identifies a row it is also a Super key.

I don't see the name 'Super key' used too much: it's usually just called a 'Unique key'.

simon at rcl
  • 7,326
  • 1
  • 17
  • 24
  • Thanks @simon. I attended a module in the Uni where the lecture referred to Unique key as Super keys.... Thanks for your response. – Bob Jun 06 '14 at 13:22
  • 7
    The term "superkey" is usually written as one word. It isn't a type of key at all - superkey means a *superset* of a key (not necessarily a *proper* superset though). Superkey = unique. Key = minimally unique. A composite key is a key consisting of more than one attribute. – nvogel Jun 06 '14 at 15:34
  • A superkey can have no columns, when it is a key with no columns. Which is when a table is constrained to have at most one row. A proper superkey has one or more columns. – philipxy Jun 17 '14 at 06:35