What are the Keys in DBMS?

In DBMS, KEYS is an attribute or set of attributes that aid in relational row (tuple) identification (table). You can use them to discover the connection between two tables. By combining one or more of the table’s columns, keys enable you to uniquely identify each row in the table. Finding a specific record or row in the table can be aided by the key. The database key is useful for locating a specific record or row in a table.

Why do we need a Key?

• Any row of data in a table can be found using keys. A table could have thousands of records in an actual application.
• It allows us to identify the relationship between tables and establish a relationship between them.
• Help us to enforce identity and integrity in the relationship.

Types of keys:

Keys are of seven broad types in DBMS:
1. Candidate Key
2. Primary Key
3. Foreign Key
4. Super Key
5. Alternate Key
6. Composite Key
7. Unique Key

Candidate Key:

• An attribute or group of attributes that can independently identify a tuple is known as a candidate key.
• Except for the primary key, the remaining attributes are considered candidate keys. The candidate keys are just as secure as the main key.

StudID	Roll No	First Name	LastName	Email
1	11	Tom	Price	abc@gmail.com
2	12	Nick	Wright	xyz@gmail.com
3	13	Dana	Natan	mno@yahoo.com

Example: In the given table, Stud ID, Roll No, and email are candidate keys that help us uniquely identify the student record in the table.

Primary Key:

In DBMS, a primary key is a column or set of columns in a table that uniquely identifies each row. The Primary Key can’t be a duplicate meaning the same value can’t appear more than once in the table. There can only be one primary key per table.

For example, in the above table, StudID is a Primary Key.

Foreign Key:

Relationships between two tables are created using foreign keys. Each value in a column or set of columns containing a foreign key must match the Primary Key of the referential table. Data and referential integrity are preserved with the aid of foreign keys.

Example:

DeptCode	DeptName
001	Science
002	English
005	Computer

Teacher ID	Fname	Lname
B002	David	Warner
B017	Sara	Joseph
B009	Mike	Brunton

Here, we have two tables, teachers, and department in a school. However, there is no way to see which teacher works in which department.

Therefore, we can establish a connection between the two tables by including the foreign key Deptcode in the Teacher table.

Teacher ID	DeptCode	Fname	Lname
B002	002	David	Warner
B017	002	Sara	Joseph
B009	001	Mike	Brunton

Super Key:

A super key is a collection of one or more keys that uniquely identifies a table’s rows. It’s possible for a Super key to have extra characteristics that aren’t necessary for unique identification.

Note: A super key is a superset of a candidate key.

Example:

For an EMPLOYEE table, for(EMPLOEE_ID, EMPLOYEE_NAME), the name of two employees can be the same, but their EMPLYEE_ID can’t be the same. Hence, this combination can also be key.

The super key would be EMPLOYEE-ID (EMPLOYEE_ID, EMPLOYEE-NAME), etc.

Alternate Key:

Each tuple in a relation may have one or more attributes, or even a combination of attributes, that uniquely identify it. Candidate keys are made up of these characteristics or combinations of characteristics. From among these candidate keys, one key is selected as the primary key, and the last candidate key, if any, is referred to as the alternate key. The total number of alternate keys is, in other words, equal to the total number of candidate keys minus the primary key.

Example:

In this table, StudID, Roll No, Email are qualified to become primary keys. But since StudID is the primary key, Roll No, Email becomes the alternative key.

StudID	Roll No	First Name	LastName	Email
1	11	Tom	Price	abc@gmail.com
2	12	Nick	Wright	xyz@gmail.com
3	13	Dana	Natan	mno@yahoo.com

Composite Key:

A COMPOSITE KEY, which unambiguously identifies rows in a table, combines two or more columns. While individual uniqueness is not guaranteed, the combination of columns guarantees it. In order to uniquely identify records in a table, they are combined.

Unique Key:

A column or set of columns known as a unique key uniquely identifies each record in a table. This key would have to contain only unique values. Always keep in mind that a unique key differs from a primary key. This is so because there can only be one null value in it. On the other hand, a primary key cannot have a null value.

Note: also read about ER Model to Relational Model

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py