Open In App

Partial, Unique, Secondary, Composite and Surrogate keys in DBMS

Improve
Improve
Like Article
Like
Save
Share
Report

  1. Partial Key :
    The set of attributes that are used to uniquely identify a weak entity set is called the Partial key. Only a bunch of the tuples can be identified using the partial keys. The partial Key of the weak entity set is also known as a discriminator.

    It is just a part of the key as only a subset of the attributes can be identified using it. It is partially unique and can be combined with other strong entity set to uniquely identify the tuples.

    Partial Key apartment number is shown with a dashed line.

    Here we have an apartment as a weak entity and building as a strong entity type connected via ‘belongs to’ relationship set. Apartment number is not globally unique i.e. more than one apartment may have same number globally but it is unique for a particular building since a building may not have same apartment number. Thus apartment number cannot be primary key of entity Apartment but it is a partial key shown with a dashed line.

  2. Unique key :
    It uniquely identifies a tuple in a relation. Unlike the Primary key, There can be more than one unique key in a table. It can accept only one null value. It cannot have duplicate values and it is non-updatable i.e. cannot be updated once it is assigned.

    Example –
    The best example of unique key is Voter ID.
    Voter Id is unique for all the citizens. If it gets lost and another duplicate copy is issued, then the duplicate copy always has the same number as before. Thus, it is non-updatable. Few citizens may not have got their Voter Id, so for them, its value is NULL.

  3. Secondary Key :
    It is a candidate key that is not selected as primary key. For example, we have schema: Student(Id, email, enroll_no, name) Candidate key of this relation is: ID, email, enroll_no
    If we select Id as primary key, then email and enroll_no becomes secondary key of the relation.

  4. Composite key :

    Up to 16 columns can be combined into a single composite index key initially using sqlplus earlier versions. However, the limit is extended and we can combine any number of columns (practically of no use)

  5. Surrogate key :
    The surrogate key is internally generated.
    Example : System date/time stamp, Counter

Last Updated : 22 Sep, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads