DBMS Keys

 

DBMS Keys

 

What are the Keys in DBMS?

A key in DBMS is an attribute or a set of attributes that help to uniquely identify a tuple (or row) in a relation (or table). Keys are also used to establish relationships between the different tables and columns of a relational database. Individual values in a key are called key values.

 Why are the Keys Required?

A key is used in the definitions of various kinds of integrity constraints. A table in a database represents a collection of records or events for a particular relation. Now there can be thousands and thousands of such records, some of which may be duplicated. There should be a way to identify each record separately and uniquely, i.e. no duplicates. Keys allow us to be free from this hassle. A key could either be a combination of more than one attribute (or columns) or just a single attribute. The main motive of this is to give each record a unique identity.

 Types of Keys in DBMS

There are broadly nine types of keys in DBMS:

1. Primary Key                       

2. Candidate (Secondary) Key                        

3. Alternate Key

4. Super Key                           

5. Composite Key                                           

6. Unique Key

7. Surrogate (Artificial) Key  

8. Foreign Key                                                

9. Partial Key

 

1. Primary Key (PK)

A Primary Key is a column of a table or a set of columns that helps to identify every record present in that table uniquely. There can be only one Primary Key in a table. Also, the Primary Key cannot have the same values repeating for any row. Every value of the Primary Key has to be different with no repetitions. The Primary Key constraint put on a column or set of columns will not allow them to have any null values or any duplicates.

 2. Candidate Key (CK) or Secondary Key

Candidate Keys are those attributes that uniquely identify rows of a table. The PK of a table is selected from one of the Candidate Keys. So, Candidate Keys have the same properties as the PK. There can be more than one Candidate Keys in a table.

 3. Alternate Key

A table can have multiple choices for a PK; however, it can choose only one. So, all the keys which did not become the Primary Key are called Alternate Keys.

 4. Super Key (SK)

Super Key is the set of all the keys which help to identify rows in a table uniquely. This means that all those columns of a table than capable of identifying the other columns of that table uniquely will all be considered super keys.

Super Key is the superset of a Candidate Key. The Primary Key of a table is picked from the Super Key set to be made the table’s identity attribute.

 5. Composite Key

Composite Key is a set of two or more attributes that help identify each tuple in a table uniquely. The attributes in the set may not be unique when considered separately. However, when taken all together, they will ensure uniqueness.

 6. Unique Key

Unique Key is a column or set of columns that uniquely identify each record in a table. All values will have to be unique in this key. A Unique Key differs from a PK because it can have only one null value, whereas a PK cannot have any null values.

 7. Surrogate Key or Artificial Key

Surrogate Keys is an Artificial Key which aims to uniquely identify each record is called a Surrogate Key. This kind of key in DBMS is unique because it is created when we don’t have any natural PK. They do not lend any meaning to the data in the table. Surrogate Key in DBMS is usually an integer. Surrogate keys in SQL are allowed when:

·         No property has the parameter of the Primary Key.

·         In the table when the Primary Key is too big or complicated.

 8. Foreign Key (FK)

Foreign Key is used to establish relationships between two tables. A Foreign Key will require each value in a column or set of columns to match the Primary Key of the referential table. Foreign keys help to maintain data and referential integrity.

 PERSONS 

person-id	last_nm	first_nm	age
1	Sharma	Hemant	30
2	Kumar	Anil	32
3	Singh	Suraj	31

 ORDERS 

order-id	order_number	personId
1	77895	3
2	44678	3
3	22456	2
4	24568	1

Here, personId of Orders table can be declared as Foreign Key. 

9. 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. 

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.