Difference

[mathewmoozh]

Can anyone help me? Can i know what is the difference between superkey and primary key?
what is actually candidatekey?

[drjohn]

fields in a table:
id
firstName
surname
dob
mobileNum
shoesize
city
eyecolor
job
roomNum
socialSecurityNumber

Superkey is any field or combination of fields that can uniquely identify a row
So the following are all superkeys
id
id, dob
id, socialSecurityNumber
socialSecurityNumber
socialSecurityNumber,job
socialSecurityNumber, job, dob
socialSecurityNumber, city, shoesize
id, socialSecurityNumber, eyecolor
id, socialSecurityNumber,roomNum
id, shoesize
id, shoesize, firstname, surname, job

and lots more.

IE ANYTHING that is a totally unique combination, even if it is a stupid combination, is a superkey.

Candidate keys are superkeys where if you remove one of the fields it is no longer a superkey (if the superkey is only one field, it is a candidate key automatically, but remembrr that you can have two or more fields in a primary key at the end)
eg
superkey (id, shoesize) - remove shoesize, and id is still a superkey, so (id, shoesize) is NOT a candidate key.

but id and separately, socialSecurityNumber are both superkeys and candidate keys, as can't remove any fields if there's only one in a superkey.

Primary key is easy - it's the candidate key that you decide to use (and any other candidate keys have to be declared unique, because they are). As you may not know the socialSecurutyNumber of someone, normally you'd use an assigned id instead, as it means you can add them to a databse immediately and update later.

Thinking of a link table, used to resolve a many to many relationship

For example book, and author tables.
a book can have many authors, and an author can write many books, so the link table is bookauthor. It has two fields, usually, the primary keys for the two tables, authorID and bookID (which could be a number issued by the system but you would use the ISBN number normally).
With just two fields, the only superkey is authorID, bookId.
The only candiate key is authorID, bookId,
And the primary key, is authorID, bookId.

If, for some reason, there was a third field in the bookauthor table, lets say,
fieldX (cos I can't think of anything else), then there are two superkeys, one candidate key and of course you will have a primary key.

The superkeys would be
authorID, bookID, fieldX
and
authorID, bookID

but authorID, fieldX may not be a superkey, as several other books may have the same authorID, fieldX combination, cos I say so. Ditto bookID, fieldX

you can work out the candidate and primary keys yourself

[pavan_teja]

I would like to add that

The PRIMARY KEY constraint is the column that uniquely identifies each row, is a combination
of UNIQUE and NOT NULL constraints. With this type of constraint, UNIQUE prevents duplicates, and
NOT NULL prevents NULL values in the column. The below example shows the creation of a PRIMARY KEY

For example consider a primary key StuId

CREATE TABLE New ( col1 INTEGER,
col2 INTEGER,
PRIMARY KEY(col1, col2) );

NOTE: CREATE TABLE/PRIMARY KEY will create implicit index 'new_pkey' for table 'new'

And one more thing,a table cannot have more than one Primary Keys...