Quick Contact

    DBMS Keys

    A relation always has a specific identifier, a field or class of fields (attributes) whose values are specific throughout all of the rows of a relation. Thus, each row is distinct and can be recognized by the values of one or more of its attributes known as a key in DBMS. Keys are always minimal series of attributes that support the uniqueness quality.

    Types of Keys

    There are following types of keys which are as follows

    DBMS Keys
    Primary Key

    An attribute which identifies a tuple uniquely is known as the primary key.

    Rules for representing Primary Key

    Two tuples cannot hold similar primary key value.

    It needs to be for each tuple to include a primary key value.

    The primary key rule cannot be invalid.

    The value in a primary key column cannot be changed or amend if any foreign key defines to that primary key.

    Example

    Consider the following Employee Table

    EMP-ID EMP-NAME AGE PHONE NO
    101 Arun 24 9865321901
    102 Vijay 25 8832186543
    103 Ajay 26 9923456788

    Note: EMP-ID is a primary key.

    A primary key can have more than one attribute such type of key is known as the concatenated key.

    Secondary Key

    The secondary key identifies the tuple but not uniquely. So, the secondary key identifies the collection of records (tuples).

    Example

    In the given table, EMP-NAME and AGE are the secondary keys in Employee relation.

    EMP-ID EMP-NAME AGE PHONE NO
    101 Arun 24 9865321901
    102 Vijay 25 8832186543
    103 Ajay 26 9923456788
    Super Key

    Super key is an attribute or the set of attributes that uniquely recognizes a tuple within a relation. The super key is a superset of the candidate key.

    Example

    In the given table, EMP-ID, and EMP-NAME (the name of two employees) can be similar, but their EMP-ID cannot be the same, and this mixture can also be a key. Equally, PHONE-NO for each employee will be unique. So, they are all super key.

    EMP-ID EMP-NAME AGE PHONE NO
    101 Arun 24 9865321901
    102 Vijay 25 8832186543
    103 Ajay 26 9923456788
    Candidate Key

    There may be more than one attribute in a relation such that they identify a tuple uniquely (i.e., each of the attribute has the property of primary key). These attributes are referred to as a candidate key. It is a subset of the super key.

    Rules for representing Candidate key

    It must include unique values.

    It should not involve null values.

    It must involve minimum areas to secure uniqueness.

    It uniquely recognizes each information in a table.

    Example

    In the given table EMP- ID, EMP-NAME, AGE and PHONE-NO are candidate keys that support us to recognize the student information in the table uniquely.

    EMP-ID EMP-NAME AGE PHONE NO
    101 Arun 24 9865321901
    102 Vijay 25 8832186543
    103 Ajay 26 9923456788
    Foreign Key

    A foreign key is an attribute (or attribute combination) of an association that is the primary key of another association. It is an attribute that develops a relationship among two tables. This technique is also known as Referential Integrity.

    Example

    Consider the following Employee Table and Department Table

    DBMS Keys

    ENO is the primary key for ‘Employee’, and DNO is the primary key for ‘Department’ and is the foreign key in ‘Employee’ table, describing to the primary key of ‘Department’ relation.

    Alternate Key

    The candidate key, which is not chosen as the primary key, is known as the alternate keys. It is also known as Secondary key.

    Example

    In the given table, EMP-ID, EMP-NAME, and Phone No are candidate keys and are adequate for being the Primary key. As a result of EMP-ID is the primary key, so EMP-NAME and Phone No. develop into the alternate key.

    EMP-ID EMP-NAME Phone No
    E-112 Savita 9821345678
    E-112 Madhur 9921345678
    E-112 Amit 8123906532

    Enroll Yourself in Live Training: DBMS Training

    Copyright 1999- Ducat Creative, All rights reserved.