Difference between super key and composite key

Question

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

Answer 1

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

Answer 2

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 composite¹ 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 UNIQUE² 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).

---

¹ Aka. compound, complex or concatenated.

² On NOT NULL columns.