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

  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

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


1


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.