Differencebetween a Candidate Key and a Primary Key

What is the difference between a candidate key and a primary key?

Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.

Primary Key – A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.

More on this link with example

Difference between Primary Key, Unique Key and candidate key

Candidate key is a minimal (i.e. irreducible) key, unique key is a pleonasm, and primary key is an obsoleted concept still surviving from the days when people thought it possible for one key to be "more unique" than any of the others.

Difference between Primary key and Candidate key

A table can have one or more candidate keys - these are keys that uniquely identify a row in the table.

However, only one of these candidate keys can be chosen to be the primary key.

What is the difference between candidate key and composite key?

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.

What is the difference between Primary key and prime attribute?

A candidate key is a key that uniquely identifies rows in a table. Any of the identified candidate keys can be used as the table's primary key.
Candidate keys that are not part of the primary key are called alternate keys. One can describe a candidate key as a super key that contains only the minimum number of columns necessary to determine uniqueness.

Prime attributes are the attributes of the candidate key which defines the uniqueness (Eg: SSN number in an employee database)

A primary key is a column in a table whose values uniquely identify the rows in the table. The primary key is chosen from this list of candidates based on its perceived value to the business as an identifier.

A primary key value:

Must uniquely identify the row;
cannot have NULL values;
Should not change over the time;
and Should be as short as possible.

If the primary key is a combination of more than one column then it is called as composite key.

Differences between key, superkey, minimal superkey, candidate key and primary key

Here I copy paste some of the information that I have collected

Key
A key is a single or combination of multiple fields. Its purpose is to access or retrieve data rows from table according to the requirement. The keys are defined in tables to access or sequence the stored data quickly and smoothly. They are also used to create links between different tables.

Types of Keys

Primary Key
The attribute or combination of attributes that uniquely identifies a row or record in a relation is known as primary key.

Secondary key
A field or combination of fields that is basis for retrieval is known as secondary key. Secondary key is a non-unique field. One secondary key value may refer to many records.

Candidate Key or Alternate key
A relation can have only one primary key. It may contain many fields or combination of fields that can be used as primary key. One field or combination of fields is used as primary key. The fields or combination of fields that are not used as primary key are known as candidate key or alternate key.

Composite key or concatenate key
A primary key that consists of two or more attributes is known as composite key.

Sort Or control key
A field or combination of fields that is used to physically sequence the stored data called sort key. It is also known s control key.

A superkey is a combination of attributes that can be uniquely used to identify a database record. A table might have many superkeys. Candidate keys are a special subset of superkeys that do not have any extraneous information in them.

Example for super key:
Imagine a table with the fields <Name>, <Age>, <SSN> and <Phone Extension>. This table has many possible superkeys. Three of these are <SSN>, <Phone Extension, Name> and <SSN, Name>. Of those listed, only <SSN> is a candidate key, as the others contain information not necessary to uniquely identify records.

Foreign Key
A foreign key is an attribute or combination of attribute in a relation whose value match a primary key in another relation. The table in which foreign key is created is called as dependent table. The table to which foreign key is refers is known as parent table.

Difference between super key and composite key

The accepted answer is not entirely accurate...

A superkey is any set of columns that, combined together, are unique. There are typically many superkeys per table and same column may be shared by many superkeys. They are not very useful by themselves, but are more of a mental tool for identifying candidate keys (see below).
A candidate key is a minimal superkey - if any column is removed it would no longer be unique. There are typically significantly fewer candidate keys than superkeys.
A key is just a synonym for a candidate key.
A composite¹ key is a key that has more than one column. In other words, it's a minimal superkey that has multiple columns.

Few more points:

Every key is unique, so calling it "unique key" is redundant. Just "key" is enough.
At the DBMS level, a key is enforced through a PRIMARY KEY or UNIQUE² constraint.
An index is usually present underneath the key (PRIMARY KEY or UNIQUE constraint), for performance reasons. But despite often going together, key and index are separate concepts: key is a logical concept (changes the meaning of data) and index is a physical concept (doesn't change the meaning of data, just performance).

¹ Aka. compound, complex or concatenated.

² On NOT NULL columns.