Can anyone help me? Can i know what is the difference between superkey and primary key?
what is actually candidatekey?
fields in a table:
Superkey is any field or combination of fields that can uniquely identify a row
So the following are all superkeys
socialSecurityNumber, job, dob
socialSecurityNumber, city, shoesize
id, socialSecurityNumber, eyecolor
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)
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
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
Edited by drjohn: n/a
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,
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...
Edited by pavan_teja: n/a