Partial, Unique, Secondary, Composite and Surrogate keys in DBMS
- Partial Key :
- 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.
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.
- 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.
- 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)
- Surrogate key :
The surrogate key is internally generated.
Example : System date/time stamp, Counter
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.
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.