Types Of Keys In Database

Database supports the following types of keys.

• Super Key
• Minimal Super Key
• Candidate Key
• Primary Key
• Unique Key
• Alternate Key
• Composite Key
• Foreign Key
• Natural Key
• Surrogate Key

Key In Sql:

Composite Key:

Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.

Columns that make up the composite key can be of different data types.

A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, 

but when it taken individually it does not guarantee uniqueness.

A primary key that is made by the combination of more than one attribute is known as a composite key.

CREATE TABLE SAMPLE_TABLE  

(COL1 integer,  

COL2 nvarchar(30),  

COL3 nvarchar(50),  

PRIMARY KEY (COL1, COL2)); (Composite Key)

Candidate Key:

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Example: In the below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.

AlterNate key:

Alternate key is a secondary key it can be simple to understand by an example:

Let's take an example of student it can contain NAME, ROLL NO., ID and CLASS.

Here ROLL NO. is primary key and rest of all columns like NAME, ID and CLASS are alternate keys.

If a table has more than one candidate key, one of them will become the primary key and rest of all are called alternate keys.

In simple words, you can say that any of the candidate key which is not part of primary key is called an alternate key. 

So when we talk about alternate key, 

the column may not be primary key but still it is a unique key in the column.

Super Key:

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table. 

Example: Primary key, Unique key, Alternate key are a subset of Super Keys