What's difference between Composite Key and Candidate Key.

Posted by Jeetmcts under Sql Server on 10/3/2011 | Points: 10 | Views : 86049 | Status : [Member] | Replies : 11
Hi,
Can you please let me know

What's difference between Composite Key and Candidate Key? and is possible to create two primary key in single table?

Thanks,
Jitendra

Jitendra Kumar


Responses

Posted by: SheoNarayan on: 10/3/2011 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
Hello Jeet,

Here is a post stating the difference between Composite and Primary key - http://www.dotnetfunda.com/interview/exam4376-difference-between-composite-candidate-and-alternate-keys-.aspx

There can be only one primary key however you can have multiple columns of the database table in your primary key, have a look at this thread for more http://stackoverflow.com/questions/217945/can-i-have-multiple-primary-keys-in-a-single-table

thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Jeetmcts, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Reena_jainy2k1 on: 10/4/2011 [Member] Starter | Points: 25

Up
0
Down
hi,

A composite Key can be either Primay or Unique Key
More then One Key columns are said to be composite keys

Candidate Key(Primary Key) is a Key which Maintains the Row
Uniue .Can be defined based on the Entity


Alternate Key or Unique Key is similar to PK ,except it
accepts null Values .So that the records can still be
entered submitting null values to this attribute.

Reena Jain
Project Manager

Jeetmcts, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vishvvas on: 10/5/2011 [Member] [MVP] HonoraryPlatinum | Points: 25

Up
0
Down
Seen the answers on the mentioned URLs. Generally while designing database, such keys needs to be identified and the best practice is go through the ER diagrams which are irrespective of any database. Here I am giving the theoratical explanations to these keys which are considered in the context of entity set (which eventually becomes table in database implementation)
1. Super key : set of one or more attributes (eventually converted to columns in database table) of an entity set which uniquely determine each entity (eventually becoming the row in the table). These are also temed as alternate keys
EmpID, EmailID, SSN, Salutation + FirstName+LastName+Middle Name, FirstName+LastName+Middle Name + Date of Birth in Employee entity set
2. Candidate Key: it is mimimal Super key
e.g. SSN in Employee entity set, accountnumber in Account entity
3. Primary key: One of the candiate keys selected to represent the entity set

Composite key is any of the above keys having more than one attribute.
Hope this helps.

Jeetmcts, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 11/24/2011 [Member] Starter | Points: 25

Up
0
Down
hi,

Candidate Key : If Empid is the PrimaryKey means Empname is an Candidate Key .because Empid is an combination of Empname..

Composite Key: More number of Primary Key is called composite Key..More then One Key columns are said to be composite keys .


Sriam.R

sriram

Jeetmcts, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksingh on: 11/25/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

Composite Key :
A composite key is a combination of more than one column to identify a unique row in a table.
Exp : Let say EmpID, EmailID, SSN are columns in Employee table and projectID in project table .
if EmpID and project id are put in projectionHours table then combination of empid and project ID called as composite key because combination of these two act as primary key in projectionHours table.

Candidate Key:
All keys in a table that become unique called as candidate key.
Exp : EmpID , EmailID and SSN all will be always unique for any employee in that case all these three columns called as candidate keys.

Alternate Key:
Among of candidate keys if any single key or combination of keys made as primary key then rest candidate key called as alternate key.
Exp : Suppose in employee table EmpID is primary key then Emailid and SSN are called as alternate key mean later on these key can be act as primary key without affecting existing data in table.



Regards,
Sunil

Jeetmcts, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/22/2012 [Member] Bronze | Points: 25

Up
0
Down
Hi,
Candidate key is a unique key that can be used as a primary key. but not necessarily used as one.

Composite key is a Primary key of two or more attributes that uniquely identifies the row.


Jeetmcts, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/22/2012 [Member] Bronze | Points: 25
Posted by: Del-Korey on: 4/21/2013 [Member] Starter | Points: 25

Up
0
Down
Thank u for ur answer ,It help me alot to understand

Jeetmcts, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Del-Korey on: 4/21/2013 [Member] Starter | Points: 25

Up
0
Down
I think that will help to in understanding the answers

http://www.dotnet-tricks.com/Tutorial/sqlserver/GcF9120312-Different-Types-of-SQL-Keys.html

Thank u

Jeetmcts, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jitendrasoft09 on: 4/30/2013 [Member] [MVP] Starter | Points: 25

Up
0
Down
Hi,
As you can not create two primary keys into single table, if you need then unique key to use, please look into below that would be helpful for you.
http://stackoverflow.com/questions/5610226/what-is-the-difference-between-candidate-key-and-composite-key

Jitendra Kumar
If my post helps you, plz mark as an answer.

Jeetmcts, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Neeraaj.Sharma on: 6/29/2013 [Member] Starter | Points: 25

Up
0
Down
Jitendrasoft09 said As you can not create two primary keys into single table
TRUE
but you can use combination of multiple column as a primary key which referred Composite key.





i was reading the example and even i got confused about what is ALL

Let`s create a Real life example and try to understand the CONCEPT.

Table Class
RollNO Class Name Age Remarks
1 1 A 6
2 1 B 6
1 2 D 7
2 2 D 7

SO here is the table. now we want to identity record uniquely , and want to put a primary key constraint on the table?
Is there Any column on we can make primary / Unique key ?
Now here we are looking for CANDIDATES to uniquely Identify the Rows in the table,

how to do that :(

but here we have two column Roll number and Class combination of both can be a primary / Unique key is`t it.
so in there scenario we are making composite key by adding combination of two column as primary / Unique key.

SO composite key is when you create primary key using more than one column.


Candidate Keys are those column that can be candidate as a primary key

Composite key made of is two or more than two columns



please ask question if any doubt .




Visit my blog
www.tutorialsqlserver.com








Jeetmcts, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response