Difference between Primary key, Secondary key, foreign key, surrogate key, Alternate key, Super key, Composite key, Alternate key?

Difference between Primary key, Secondary key, foreign key, surrogate key, Alternate key, Super key, Composite key, Alternate key?

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.

An attribute or combination of attributes that uniquely identify an entity/record in a relational table.

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.

Single key that is unique and not-null.

E.g. of Primary Key - Database designer can use one of the Candidate Key as a Primary Key. In this case we have “ID” and “Name, Address” as Candidate Key, we will consider “ID” Key as a Primary Key as the other key is the combination of more than one attribute.

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.

E.g. of Candidate Key

1.      ID

2.      Name, Address

For above table we have only two Candidate Keys (i.e. Irreducible Super Key) used to identify the records from the table uniquely. ID Key can identify the record uniquely and similarly combination of Name and Address can identify the record uniquely, but neither Name nor Address can be used to identify the records uniquely as it might be possible that we have two employees with similar name or two employees from the same house.

Alternate Key:

Alternate Key can be any of the Candidate Keys except for the Primary Key.

E.g. of Alternate Key is “Name, Address” as it is the only other Candidate Key which is not a Primary Key.

Composite key or concatenate key:

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

Super key:

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

A table can have many Super Keys.

E.g. of Super Key

1.      ID

2.      ID, Name

3.      ID, Address

4.      ID, Department_ID

5.      ID, Salary

6.      Name, Address

7.      Name, Address, Department_ID

So on as any combination which can identify the records uniquely will be a Super Key.

Foreign Key:

 A foreign key is an attribute or combination of attribute in a relation whose value matches 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.

E.g. of Foreign Key – Let consider we have another table i.e. Department Table with Attributes “Department_ID”, “Department_Name”, “Manager_ID”, ”Location_ID” with Department_ID as an Primary Key. Now the Department_ID attribute of Employee Table (dependent or child table) can be defined as the Foreign Key as it can reference to the Department_ID attribute of the Departments table (the referenced or parent table), a Foreign Key value must match an existing value in the parent table or be NULL.

Unique Key:

A unique key that may or may not be NULL

Compound key:

Compound key (also called a composite key or concatenated key) is a key that consists of 2 or more attributes.

Composite Key: 

If we use multiple attributes to create a Primary Key then that Primary Key is called Composite Key (also called a Compound Key or Concatenated Key).

Simply PK made up of multiple attributes.

E.g. of Composite Key, if we have used “Name, Address” as a Primary Key then it will be our Composite Key.

Secondary Key: 

The attributes that are not even the Super Key but can be still used for identification of records (not unique) are known as Secondary Key.

E.g. of Secondary Key can be Name, Address, Salary, Department_ID etc. as they can identify the records but they might not be unique.

Surrogate Key:
Surrogate Key is the solution for critical column problems.For example the customer purchases different items in different locations, for this situation we have to maintain historical data. By using surrogate key we can introduce the row in the data warehouse to maintain historical data.

It maintaining the uniqueness in the table. It is used to track the old value with the new one. And it is derived from primary key.

SHARE

About df