keys in DBMS: Candidate key, Primary key

 keys in DBMS: Types of keys in database management system .

Keys:

The integrity of the information stored in a database is controlled by keys. A key or key field is a column value in a table that is used to either uniquely identify a row of data in a table, or establish a relationship with another table.

A Key is normally correlated with one column in table, although it might be associated with multiple columns. Usually a key is used to sort data, that is arranging  the records in ascending or descending order. It is also referred as sort key, index or key word. Most database systems allow more then one key so that the records can be stored in various ways. 

Types of keys:   

1. Super Key
2. Candidate Key
3. Primary Key
4. Foreign Key
5. Composite Key
6. Unique Key
7. Alternate Key

1.Super Key-

A super key is a set of one or more attributes that taken collectively, allow us to identify uniquely an entity in the entity set. 

A combination of one or more columns in a table which can be used to identify a record in a table uniquely, a table can have any number of super keys.

                  

                          STUDENT TABLE

Roll no	Name	Branch	DOB	Year
068974	Hema	IT	01/07/2002	1st
068932	Ishika	IT	11/08/2001	1st
065321	Megha	CS	12/06/2000	2nd
065478	Tanya	CS	17/04/2002	3rd

Now from the above table-

                      super key1=(Roll no, Name, Branch)

                      super key2=(Name, Branch, DOB)

                      super key3=( Name, DOB, Year)

2.Candidate Key-

A  combination of columns which can help uniquely identify a record in a table without the need of any external data is called a candidate key. Depending on the need in situation a table may have one or more candidate keys and one of them can be used as a primary key of the table. A candidate key is a subset of a super keys.

For example-

Usually in an organization, every employee has a unique code. This field can be used to uniquely identify employees record. Since no two employees can have the same code, their record can simply be fetched by using their code. Note that every key field is a candidate key but there cannot be more than one primary key in a table.

      From the above Student table.

                 Candidate Key1={Roll No, Name}  

                 Candidate Key2={Name, DOB} 

                 Candidate Key3={Name, Branch} 

3.Primary Key-

It denotes a key that is chosen by the database designer as the principal means of identifying unique records within a table. The primary key should be chosen in such a way that its values must not (or rarely) change. 

For example-

The employees code field can be designated as the primary key because all employee codes are unique and the value once entered is never changed until the person is in the organization. There should not be any duplicacy in the record of primary key.

      From the above table student 

                  Primary key= {Roll no.}

Since primary key is such a key by which we can uniquely identifies each entity. 

For example, by Roll no. we can determine other attributes of the student. 

4.Foreign Key-

A column of one table points to the primary key column of another table to implement referential data integrity. 

For example-  

We can have a department id column in the employee table which is pointing to department id column in a department table where it a primary key. 

 

5.Composite key-

When we have a primary key of a table define the key using more than one columns then it is known as a composite key , each columns data can be duplicated, but combined values cannot be. The columns which are participating in a composite primary key are not simple keys.

For example- 

 We can have a situation where there is a need to define the key using first name + last name.

6.Unique Key-

A combination of columns which can be used to uniquely identify a record in a table, it can have one null value. Primary key can be considered a special case of unique key with a not null constraint.

    Generally, but not always and need not be, unique key goes with a non clustered index.

7.Alternate Key-

We cannot define the alternate  key separately from a candidate key, for a table, if there are two candidate keys and one is chosen as a primary key the other candidate key is known as the alternate key of that table.

For example-

We can consider the employee SSN as alternate key as we have taken Employee ID as our primary key.

Conclusion-

These were some of the point discussed in the post-  Keys in DBMS and  Types of key.

There is more coming with the next post keep reading...