Keys in DBMS
What are Keys in DBMS?
In this article, we will learn about Keys in DBMS.
Keys Identify any given tuple or record uniquely and also provide necessary functionality between various tables, in sql there are various types of keys, some of which are –
- Primary Key
- Foreign Key
- Candidate Key
- Super Key
- Alternate Key
- Composite Key
We also use keys as they surely help us in enforcing identity & integrity in the relationship.
Let’s have a look further to understand how each of them work –
How Keys in DBMS work
For an explanation of how each of the keys is working, we will take the help of the following tables –
To uniquely identify each tuple or row in a data table. We use primary key as identifying attribute.
For example –
We all are given a unique rollnumber or registration number at the time of admission in a school or college. That is primary key for any student table.
In the example mentioned above
STUDENT_ID works as primary Key.
The minimal (minimum) number of attributes that can uniquely identify a record for a data table is/are candidate key(s).
For example –
- STUDENT_ID can identify a unique record in a datable
- For course table we need both STUDENT_ID and COURSE_ID to uniquely identify a record i.e. (STUDENT_ID, COURSE_ID) become candidate key, which is composite in nature.
- Candidate keys must have non null elements in each record/tuple
- Only Unique values are allowed
- Candidate key may have multiple attributes
- It should contain minimum fields to ensure uniqueness
- Should be able to uniquely identify each record in a table
Set of attributes that can help us uniquely identify a record or tuple in the database is called super key.
Now, super key may sound the same as candidate key. Yes, it does !!! There is only slight different.
- Candidate key says – Minimal
- Super key – Doesn’t say any number
Thus, for course table
NAME can be a super key of 3 composite attributes in nature, as they will uniquely identify the record.
But, it is not a candidate key as just by using
COURSE_ID we can uniquely identify rows. Thus, minimum number is 2.
- Every Candidate key is a super key
- The vice versa is not true however
- If we just add one or more attributes to a candidate key then, it becomes a super key.
There can be multiple Candidate keys right? Out of which we may select one as a primary keys.
The other set of candidate keys than primary key itself are known as candidate keys.
Keys which more than one attribute that can uniquely identify a record in a table is a composite key.
Note – Some people may get confused between candidate key and composite key –
Candidate Key: A nominee for primary key field is known as candidate key.
Composite Key: Creating more than one primary key is jointly known as composite key.
A candidate key is a unique key that can be used as a primary key. Composite key is a key of two or more attributes that uniquely identifies the row. A key is a set of columns that can be used to uniquely identify each row within a table.
To define a relationship with another table of a database. We use foreign key. A foreign key is a column of a table that points towards the primary key of another table.
For example –
In the above tables Department_ID is the primary key for Department Table and Professor_ID is the primary key of Professor Table.
Now, if we add DeptarmentID in table 2 in that case we can create a relationship between table 1 and table 2. Understanding that which teacher belongs to which department. Thus adding Department_ID, which is foreign key in New table and the primary key Department table.