DBMS Keys : Super, Candidate, Primary, Alternate, Foreign, Composite (Example)

Keys In DBMS -

Key plays an important role in relational database. it is used for identifying unique rows from table. It also establishes relationship among tables.


There are various keys in DBMS -

1. Super key
2. Candidate Key
3. Primary Key
4. Alternate or Secondary Key
5. Foreign Key
6. Composite Key



NOTE:-   

-> The terms ‘relation’ and ‘table’ are used interchangeably.

-> The terms ‘tuple’ and ‘record’ are used interchangeably.
So, don’t get confused..!





1. Super Key -

A super key is a set of one of more columns (attributes) to uniquely identify rows in a table. A Super key may have additional attributes that are not needed for unique identification.
a super key may consist of any number of attributes.

Emp_SSN	Emp_No	Emp_name
9745824578	AB01	John
9876512345	AB08	Rakesh
8749621478	AB07	Bunty

-> In the above-given example, emp_SSN and emp_No ,name are superkeys.

2. Candidate Key - 

A super key with no repeated attribute is known as candidate key.

The Primary key should be selected from the candidate keys. Every table must have at least a single candidate key.

Properties of Candidate key:

-> The value of candidate key can never be NULL.

-> It must contain unique values.

-> Candidate key may have multiple attributes.

-> It should contain minimum fields to ensure uniqueness.

-> Uniquely identify each record in a table.

Ex:  In the given table Stud_ID, Roll No, and email are candidate keys which help us to uniquely identify the student record in the table.

Stud_ID	Roll No	First Name	LastName	Email
1.	101	AK	price	xyz@gmail.com
2.	102	Nick	Wright	abc@gmail.com
3.	103	Danger	Natan	opt@gmail.com

3. Primary key -

A column or group of columns in a table which helps us to uniquely identifies every row in that table is called a primary key. This DBMS can't be a duplicate. The same value can't appear more than once in the table.

Rules For Defining primary key:- 

-> The value of primary key can never be NULL.

-> The value of primary key must always be unique.

-> The values of primary key can never be changed i.e. no updation is possible.

-> The value of primary key must be assigned when inserting a record.

-> A relation is allowed  to have only one primary key. 

Example:  <code>Stud_ID</code> is a Primary Key.

Stud_ID	Roll No	First Name	LastName	Email
1.	101	AK	price	xyz@gmail.com
2.	102	Nick	Wright	abc@gmail.com
3.	103	Danger	Natan	opt@gmail.com

4. Alternate Key -

-> Unimplemented candidate keys are called as alternate keys.

                                                or

-> All the keys which are not primary key are called an alternate key.

                                                or

-> Out of all candidate keys, only one gets selected as primary key, remaining keys are known as alternate or secondary keys.

Stud_ID, Roll No, Email are qualified to become a primary key. But since Stud_ID is the primary key, Roll No, Email becomes the alternative key.

Stud_ID	Roll No	First Name	LastName	Email
1.	101	AK	price	xyz@gmail.com
2.	102	Nick	Wright	abc@gmail.com
3.	103	Danger	Natan	opt@gmail.com

5. Foreign Key -

Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.

                                       or

A foreign key is a column which is added to create a relationship with another table. Foreign keys help us to maintain data integrity and also allows navigation between two different instances of an entity. Every relationship in the model needs to be supported by a foreign key.

Ex:

DeptCode	DeptName
01	Enviornment
02	Science
05	Computer

Teacher ID	Fname
B002	Rajesh
B017	Shakti
B009	John

In  this example, we have two table, teacher and department in a school. However, there is no way to see which search work in which department.

In this table, adding the foreign key in Deptcode to the Teacher name, we can create a relationship between the two tables.

Teacher ID	DeptCode	Fname
B002	01	Rajesh
B017	02	Shakti
B009	03	John

-> This concept is also known as Referential Integrity.

RULES:-

-> Foreign key references the primary key of the table.

-> Foreign key can take only those values which are present in the primary key of the referenced relation.

-> Foreign key can take the NULL value.

-> There is no restriction on a foreign key to be unique.

-> Referencing relation may also be called as the foreign table.

6. Composite Key -

A key which has multiple attributes to uniquely identify rows in a table is called a composite key. The difference between compound and the composite key is that any part of the compound key can be a foreign key, but the composite key may or maybe not a part of the foreign key.

Difference Between Primary key & Foreign key

Primary Key	Foreign Key
Primary Key is always unique.	Foreign key can be duplicated.
Primary Key never accept null values.	A foreign key may accept multiple null values.
Primary key is a clustered index and         data in the DBMS table are physically organized in the sequence of the clustered index.	A foreign key cannot automatically create an index, clustered or non-clustered. However, you can manually create an index on the foreign key.
You can have the single Primary key                in a table.	You can have multiple foreign keys in a table.
Primary Key uniquely identify a record in Table.	Foreign key is a field in the table that  is the primary key ofanother table.