Click here to Skip to main content
12,630,410 members (28,513 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: DBMS
What is the difference between the terms primary key, super key and candidate key used in Database Management System - DBMS.

I did google but couldn't distinguish them. The terms candidate key and primary key appear to be the same.
Posted 1-Nov-12 9:49am
Updated 11-Jun-16 19:04pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Super Key: An attribute or set of attributes that uniquely identifies a tuple within a relation

Candidate key: A super key such that no proper subset is a super key within the relation

Primary key: The candidate key that is selected to identify tuples uniquely within the relation, the candidate keys which are not selected as PKs are called "Alternate keys"
  Permalink  
Comments
ridoy 1-Nov-12 15:42pm
   
exactly..+5
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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.


Candidate Keys
Candidate Keys are super keys for which no proper subset is a super key. In other words candidate keys are minimal super keys.
Primary Key:
It is a candidate key that is chosen by the database designer to identify entities with in an entity set. Primary key is the minimal super keys. In the ER diagram primary key is represented by underlining the primary key attribute. Ideally a primary key is composed of only a single attribute. But it is possible to have a primary key composed of more than one attribute.

Composite Key
Composite key consists of more than one attributes.

Example: Consider a Relation or Table R1. Let A,B,C,D,E are the attributes of this relation.

R(A,B,C,D,E)
A→BCDE This means the attribute 'A' uniquely determines the other attributes B,C,D,E.
BC→ADE This means the attributes 'BC' jointly determines all the other attributes A,D,E in the relation.

Primary Key :A
Candidate Keys :A, BC
Super Keys : A,BC,ABC,AD

ABC,AD are not Candidate Keys since both are not minimal super keys.
  Permalink  
v2
Comments
sumitkrishna2002 11-Jun-15 23:30pm
   
good explaination
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

Check this blog[^] Video[^]

I'll take example of an Employee table:
Employee (
Employee ID,
FullName,
SSN,
DeptID
)

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 do have a single 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.
  Permalink  
v3
Comments
Dave Kreskowiak 15-Sep-15 9:34am
   
Why are you adding solutions to 3 year old questions? Your answer doesn't add anything to the discussion that hasn't already been said.
manub22 15-Sep-15 9:38am
   
I believe its an open forum, be it 3-4 years old, I don't care, as far as I'm helping SQL community and getting myself benefited from it. Let it trend down if its not useful, otherwise let it trend it up. What's your problem?
Dave Kreskowiak 15-Sep-15 9:44am
   
Because every post to a QA question pushes it to the top of the question list where other people post additional clarifying questions for the original poster and don't notice that they're never going to get answers to them.

Like I said, you're not adding anything to the discussion at hasn't already been said.

manub22 15-Sep-15 9:53am
   
Oh, B.T.W thanks for down-voting my answer, I just checked it!
Dave Kreskowiak 15-Sep-15 10:23am
   
Yep and I already told you why.
CHill60 15-Sep-15 10:30am
   
You have no idea who down-voted your solution so don't start accusing people.
The fact is that your solution has resurrected an old post that has an accepted answer, and your solution has added no new information to the thread. It's not advisable and could lead to your solution being further down-voted or even reported as a "repost"
None of these things are "helping SQL community"
Member 12560321 1-Jun-16 21:00pm
   
Just registered here to upvote your solution and to support you in light of the rudeness of those moderators.

Keep helping others understand, no matter if the question is old or new!! I read all previous solutions but they were so theoretical that I needed one simple example to clarify it.

Thanks again, bro!
manub22 2-Jun-16 2:44am
   
thanks dear :)
Vikas Kumar 4-Jun-16 5:44am
   
But I think this is much clear answer with example.
manub22 15-Sep-15 9:48am
   
I believe you are the moderator of this portal. If yes and if you don't like my answer and this question coming on top because of it you can delete my response. Thanks!
Member 11542173 6-Jan-16 12:58pm
   
quite summarized, thanks manub22.
manub22 14-Feb-16 1:47am
   
Thanks @Member
Member 12329471 15-Feb-16 20:52pm
   
I appreciate it, too. It may be too old for the original poster, but it's not too old for those of us who are trying to learn this stuff now. No clue what Kreskowiak's issue is.
manub22 15-Feb-16 22:33pm
   
Thanks friend!
Though the accepted solution is correct but not descriptive, so I just tried to help here so that people understand in simple terms, that's it.
But if you see the rating only these 2 guys commented and would have given my answer 1 rating.
Member 12329471 15-Feb-16 22:54pm
   
You're welcome! I'm just learning database stuff now and your answer was the one I found most helpful - it explained it in basic enough terms that someone without prior RDBMS knowledge could understand. Definitely appreciate the HW assist :)
Member 11646435 21-Feb-16 10:58am
   
Thankyou It helped me.
Maiko Martin 27-Mar-16 19:44pm
   
Thank you Manub! Your answer explained it for me best. I don't know why moderators have to be so unnecessarily rude sometimes, but thanks for posting this!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

A superkey is basically all sets of columns for which no two rows share the same values for those sets.

A table can have multiple superkeys of course, and minimal superkey is considered a candidate key.

Read here for a good source of info on superkeys:

Superkey example
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.161205.3 | Last Updated 12 Jun 2016
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100