In this article, we will discuss what are database keys in DBMS. and why do we need keys in DBMS? and also, different types of database keys, we will make you learn.
DBMS Key
The DBMS Key is an attribute or a set of attributes, that are used to find out the particular record, or row(tuple) in a database table(relation). We can find out whatever record we want from the database with the help of keys because they help in uniquely identifying the database record.
Why do we need a key in DBMS?
In DBMS key plays a very important role in finding out the required database. It establishes a relationship between tables. Whenever there is a database table being created, there is also a DBMS key created to find the database record. The key can be created with a combination of one or more columns.
We store data in the form of tables(known as Relation) in a database, and data is stored row-wise(Entity Wise) in a database table, and if you have to find out the particular data(Row/Entity) in a database, you need a key to find out the database record in a table and this key is made from attributes in an entity.
Types of Keys
There are some Database Management System Keys.
- Super key: Group of single or multiple keys used to identify rows in a table.
- Primary Key: Group of columns that uniquely identify each row in a table.
- Candidate Key: A set of Attributes that uniquely identifies a tuple in a table, it is a super key without any repeated attributes.
- Alternate Key: Column or a group of a column that uniquely identifies each row in a table.
- Foreign key: Column which shows the relationship between two tables. Used to maintain data integrity and allows navigation between two different instances of an entity.
- Compound Key: It has two or more two attributes that allow one to uniquely identify a specific record.
- Composite Key: It’s an artificial key that uniquely identifies every record called a surrogate key. This type of Key is created when there is not any natural primary key.
- Surrogate Key: An artificial key uniquely identifies every record, and is created when there are no natural primary keys.
What is a Super Key?
A Superkey is a supreme key which is a group of multiple keys or a single keys that identifies rows in a table, which may have additional attributes that are not needed for unique identification, and use any attribute as a super key but one attribute data value must be different from the other.
For Example, In the below table Students, there are {Roll_no, Email_add, Phn_no, Name, Course, Address} Attributes.
And in this table, the super key is:
- {Roll_no}- In this attribute, all the values are always unique.
- {Email_add}- Each student’s email is different every time.
- {Phone_no}- Phone no is always different.
- {Roll_no + Name}- Roll_no is unique but the name can be the same.
- {Phone_no + Address + Course}
- {Email + Address}
- etc…
So, from the above example, we understand that any attribute can be a super key but one attribute should be unique from other
What is a Primary Key?
The primary key is the most important key and the main key in the database used to uniquely identify each record in a table.
The Database Administrator picks out the main key(Primary key) which is sufficient alone to identify any record in a database table.
The primary key is selected from one of many candidate keys (Roll_no, Phn_no, Email_id, etc) but only one candidate key is selected from the group of candidate keys. The primary key can’t be a duplicate key means that- the same value can’t appear more than one time in a table. A table has only one primary key.
Rules to Set a Primary Key.
- Only unique attributes can be a primary key, which doesn’t have any duplicate value.
- The primary key value can never be NULL(empty).
- There can be only one primary key in a table.
- If there is not a sufficient single attribute that uniquely identifies records, in that case, DBA has the authority to pick more than one attribute as a primary key. So primary key may be a combination of more than one attribute.
In What situation we can choose more than one attribute as a primary key?
If there is a condition when the database table doesn’t have a single attribute, then in that case DBA can create more than one attribute as a primary key.
For example, the below order table has a daily record of purchasing orders, So the task is how to set a primary key in this table.
In the above Order table, we can’t set Customer_ID as the primary key because a customer can order many times, and this can’t be a primary key because only one time a primary key is set in a table.
Secondly, we also, can’t set Product_ID as the primary key because the product can also be ordered many times by a customer.
Then, the only way is to set Customer_ID + Product_ID as a primary key because both the attributes together can identify the record of a row uniquely in a table.
What is a Candidate Key?
A candidate key is a group of one or more than one attribute, that is used to uniquely identify a record of a row in a table. It’s the same as Super Key but with no extra attributes.
A candidate key is a subset of the super key, which is selected from a set of super keys, with minimum attributes, which means there are no repeated attributes.
Rules to set Candidate key
- The candidate key must have a unique value.
- Doesn’t contain null values.
- There can be many candidate keys in a table.
- It is the same as a super key because a super key helps to find out the candidate keys.
For example, in the above database table of students, there is three candidate key it.
- Roll_no
- Phn_no
We can’t add two attributes to set as a candidate key like (Roll_no + Phn_no, or Phn_no + Email) because we have to use minimal attributes and only a single attribute like one of them (Roll_no, Phn_no, Email) is sufficient for finding a record in a database table.
What is a Foreign Key?
A Foreign Key is a combination of two tables, in which two tables key is used to form a foreign key which acts as a cross-reference between two tables. A foreign key is used to maintain data integrity and allows navigation between two different instances of an entity.
Actually, the Foreign key is not used for identifying any record, it’s just used to point out the key attributes of another table.
Rules to set Foreign key.
- Foreign keys can have duplicate values.
- A foreign key can also contain a NULL value.
- Foreign key acts as a cross-reference between tables.
For example, In the below table Customer_ID and Product_Id both act as foreign keys because you can see that these keys are attributes of two different tables. Therefore, these attributes act as a cross-reference between two tables. This is also called Referential Integrity.
You can also read these topics:
Advantages and Disadvantages of Database
Different types of Keys in DBMS