What is the difference between candidate key and composite key?

Question

I am reading about candidate keys and composite keys. I came to know that

• a candidate key can qualify as a primary key and it can be a single column or combination of columns
• and a composite key is also a combination of columns.

For composite key I have referred this link

how do I make a composite key with SQL Server Management Studio?

Thus when both the candidate key and composite key are a combination of columns, they can qualify as a primary key. Then what is the exact difference ? Can you please explain with examples ?

Answer 1

As I know candidate key is a unique key that can be used as a primary key. but not necessarily used as one.

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

Answer 2

CANDIDATE KEY : Candidate key is the single column/multiple column(combined) which helps us to uniquely identify rows in a table.

• We can have more than 1 candidate keys

<----- Now among all these candidate keys, one of them is made primary key(which depends upon the developer which one is suited for them) ------>

COMPOSITE KEY : Composite key is set of 2 or more columns (it can't be single column) which helps us to identify rows in a table.

• So, yes composite keys are candidate key
• In case Composite key is made as primary key, we call it composite primary key(primary key made up of more than 1 column combined).

-Hope this helps

Answer 3

Lets keep it simple.

Primary Key: Uniquely identifies each row using one column

Composite Key: Uniquely identifies each row using more than one columns

Candidate Key: Can be a Primary key or a Composite key, but is not being used as one. As the name suggests it is only a 'candidate'.

Answer 4

Candidate Key: A nominee for primary key field is known as candidate key.

Composite Key: Creating more than one primary key is jointly known as composite key.

Update : A candidate key is a unique key that can be used as a primary key. Composite key is a key of two or more attributes that uniquely identifies the row. A key is a set of columns that can be used to uniquely identify each row within a table.

Answer 5

A key is a set of columns that can be used to uniquely identify each row within a table.

Every table has at least one key. Let's say we've identified each possible key for the table. Each of these keys is a candidate key.

As we examine each of these keys, the key may consist of no columns (!), one column, or more than one column, which when considered together uniquely identify each row. The term composite key specifically refers to a key which consists of more than one column.

In SQL, it was decided that one key should be selected and treated "more equal" than the other keys of the table. This key is called the primary key. Other keys can also be declared on the table, these are usually referred to as Unique Contsraints.

(!) In SQL, you aren't allowed to declare a key with no columns - although it would occasionally be useful (think of a table that should only ever have a single row, where each column is representing configuration information)

---

As an example of a table which has multiple keys, all of which are composite. Imagine an appointment system, where a client and a counsellor meet in a room at a particular time:

CREATE TABLE Appointments (
    ClientID int not null,
    CounsellorID int not null,
    RoomID int not null,
    AppointmentTime datetime not null
)

The candidate keys for this table are {ClientID,AppointmentTime}, {CounsellorID,AppointmentTime} and {RoomID,AppointmentTime}. Any of those combinations of columns could be used to uniquely identify a row in the table, and all of them are composite keys.

Which one we choose to declare as primary key will depend (probably) on our own interpretation of the main "focus" of the system. Are we mainly concerned with Room usage, or Clients, or Counsellors? In any case, we'll select one and declare it the primary key. We'll also hopefully declare the other keys as unique constraints.

Or, we could decide to use a surrogate, and declare an AppointmentID column, using whatever auto-numbering facilities are available in the database. That could then be the (non-composite) primary key. But we should still declare the other keys for the table.

Answer 6

A candidate key is just that: a column or combination of columns that could be used as the primary key, ie a candidate or potential primary key, A composite key is by definition two or more columns that could be used to identify a row. Most commonly when talking about composite keys the question is whether to have it as the primary key to have a surrogate key instead. On occassion you may hear people refer to a composite key that is not a primary key (ie not unique for all rows), in which case it is (hopefully) just a way to refer to a multi-colmn join on non-primary key fields (instead of doing cross join and filtering the rows in the where clause).

Answer 7

CANDIDATE KEY :- Candidate key is a unique key and is a "Candidate" for being a primary key. COMPOSITE KEY :- "Composition" of two or more columns as primary key, is consider as Composite key.