CODEWITHSUNDEEP
sqldatabase-designrelational-databasecomposite-keyunique-key
Bob
Bob

Reputation: 592

Difference between superkey and composite key

What is the difference between a superkey and a composite key?

Upvotes: 12

Views: 31275

Answers (4)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

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.

Upvotes: 22

Vaibhav Singh
Vaibhav Singh

Reputation: 202

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.

Upvotes: 0

Manoj Pandey
Manoj Pandey

Reputation: 1397

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

Upvotes: 11

simon at rcl
simon at rcl

Reputation: 7344

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'.

Upvotes: -2

Related Questions