I am new to DBMSs and I am still learning the theory.

I am getting really confused with this key business and after googling I have narrowed it down to just 2 keys I don't get (primary and super key).

I have a few questions on DBMS. I would be grateful if you can answer them for me.

1) What is the difference between Primary key and Super key in DBMS? Highly appreciate it if you can use a comprehensive example to explain properly

2) Can Primary key and Super key both have multiple columns combined to form Primary key and Super key?

3) Is Primary key a subset of Super key or vice versa?

  • One thing that is glossed over a lot is that the tuple (row) itself is a superkey. Since the intent is to be able to uniquely identify the row, i.e. to be able to uniquely identify the tuple containing a particular combination of values, one way to do that is to already have all the values available. Hence the tuple is a superkey for itself, since once we know the values in the tuple, then clearly we know how to find the tuple having those values. It seems silly at first but it establishes an upper bound on what can be a superkey for a tuple -- the tuple itself. – Dave Feb 13 '17 at 2:49
  • @Dave The set of all column names of a relation/table (and hence each of its tuples/rows) is a superkey of it. Not "the tuple (row) itself". – philipxy Feb 14 at 23:20
  • @philipxy you are right and I misspoke -- I stand corrected. Thanks for adding the clarification. – Dave Mar 3 at 17:07

A Super Key is simply a non-minimal Candidate Key, that is to say one with additional columns not strictly required to ensure uniqueness of the row.

A Primary Key is a minimal Candidate Key, which is to say all constituent columns are strictly required in order to ensure uniqueness.

As a database developer/designer of 30 years experience, I had never even heard the term Super Key until I saw this question, and looked it up. The concept of Super Key seems more germane to the topic of performance and Physical Schema design as it directly maps to the concept of a unique nonclustered index with additional columns for improved query covering.

  • 3
    This is quite inaccurate. You are using superkey, CK & PK in place of proper superkey, superkey & CK. A superkey is a UNIQUE set. (A CK is a "minimal" superkey. There is no notion of "minimal CK". Every CK is a superkey. So a superkey does not have to have more columns than a CK. A PK is a CK one calls the PK. If it had to be a "minimal CK" then "minimal" would have to mean "arbitrary".) SQL PK & UNIQUE NOT NULL declare superkeys. (An SQL PK might or might not be a PK.) Superkeys are critical to the definition of CK, which is critical to design including normalization. See my answer. – philipxy Jul 25 '17 at 9:10

Super Keys : Super key stands for superset of a key. A Super Key is a set of one or more attributes that are taken collectively and can identify all other attributes uniquely.

For Example, We are having table

Book (BookId, BookName, Author)

So in this table we can have

   (BookId)
   (BookId,BookName)
   (BookId, BookName, Author)
   (BookId, Author)
   (BookName, Author)

As our Super key. Each super key is able to uniquely identify each tuple (record).

Candidate Keys Candidate keys are a super key which are not having any redundant attributes. In other words candidate keys are minimal super keys. For Example, In above illustration

   (BookId)
   (BookName,Author)

These two keys can be candidate keys, as remaining keys are having redundant attributes. Means in super key (BookId, BookName) record can be uniquely identify by just bookid and therefore Bookname is redundant attribute

Primary Key: It is a candidate key that is chosen by the database designer to identify entities with in an entity set. OR A key which is used to uniquely identify each record is known as primary key.

From above Candidate keys any one can be the primary key. And the another one which is not chosen as primary key will be know as Alternate key

  • This answers the question and deserves upvote. – srk Aug 19 at 15:46

From this stackoverflow.com answer of mine:

A candidate key is a set of columns that uniquely identifies rows and that contains no smaller ("proper") subset of columns that uniquely identifies rows.

A superkey is a set of columns that uniquely identifies rows. So a candidate key is a superkey that contains no smaller superkey.

In SQL you can't declare an empty candidate key. Also, UNIQUE NOT NULL and PRIMARY KEY (which in terms of constraints just means UNIQUE NOT NULL) declare superkeys, not keys per se. If such a declaration's column set doesn't contain a smaller column set declared as a superkey then the superkey it's declaring is a candidate key.

From this stackoverflow.com answer of mine re a given table:

For sets of columns X and Y we can write X -> Y. We say that X is the determinant set and Y is the determined set of/in functional dependency (FD) X -> Y.

We say X functionally determines Y and Y is functionally determined by X. We say X is the determinant of X -> Y. In {C} -> Y we say C functionally determines Y. In X -> {C} we say X functionally determines C. When X is a superset of Y we say X -> Y is trivial.

We say X -> Y holds in table T when each subrow of values for X always/only appears with the same subrow of values for Y. Or we say X -> Y is a FD of/in T. When X is a determinant of some FD in table T we say X is a determinant of/in T.

A superkey of a table T is a set of columns that functionally determines every attribute. A candidate key (CK) is a superkey that contains no smaller superkey. We can pick one CK as primary key (PK) and then call the other CKs alternate keys. A column is prime when it is in some CK.

(As I commented there, "The four boldface sentences for FD, holds, superkey and CK would have sufficed.")

(A table with an empty CK is constrained to contain at most one row. A column set determined by the empty set is constrained to have the same subrow value in every row.)

Key A key is a single or combination of multiple fields. Its purpose is to access or retrieve data rows from table according to the requirement. The keys are defined in tables to access or sequence the stored data quickly and smoothly. They are also used to create links between different tables.

Types of Keys The following tables or relations will be used to define different types of keys.

Primary Key The attribute or combination of attributes that uniquely identifies a row or record in a relation is known as primary key.

Secondary key A field or combination of fields that is basis for retrieval is known as secondary key. Secondary key is a non-unique field. One secondary key value may refer to many records.

Candidate Key or Alternate key A relation can have only one primary key. It may contain many fields or combination of fields that can be used as primary key. One field or combination of fields is used as primary key. The fields or combination of fields that are not used as primary key are known as candidate key or alternate key.

Composite key or concatenate key A primary key that consists of two or more attributes is known as composite key.

Sort or Control key A field or combination of fields that is used to physically sequence the stored data called sort key. It is also known as control key.

A superkey is a combination of attributes that can be uniquely used to identify a database record. A table might have many superkeys. Candidate keys are a special subset of superkeys that do not have any extraneous information in them.

Example for super key: Imagine a table with the fields Name, Age, SSN and <Phone Extension>. This table has many possible superkeys. Three of these are SSN, Phone Extension and Name. Of those listed, only SSN is a candidate key, as the others contain information not necessary to uniquely identify records.

Foreign Key A foreign key is an attribute or combination of attributes in a relation whose value match a primary key in another relation. The table in which foreign key is created is called a dependent table. The table to which foreign key it refers is known as parent table.

for Minimal Super key refer this link it is more clearer there http://www.answers.com/topic/superkey-1

protected by Community Sep 30 '17 at 5:26

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.