What is the difference between candidate key and composite key?

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.

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.