Introduction
The major contribution of keys in relational databases is using it to recognize any record or row (tuple) of data from a table. Keys are also used well to create and specify relationships between the relations (tables) .
What are the keys in DBMS?
It is important to figure out a way to define how tuples (rows) within a given relation are considerable; this is demonstrated on the basis of their attributes.
Specifically, the values of the attribute values of a tuple (row) are supposed to be such that they can uniquely identify the row ( tuple) .
In plain English, no two rows in a relation are allowed to have absolutely the same value for all attributes.
Why do we need keys?
The primary reason is quite obvious without DBMS keys. You cannot access the rows of data stored in the table unambiguously.
Without the key most of the time you will either get multiple rows of data or wrong rows of data.
One more major reason, which is kind of an extension of the primary reason, is we can force the identification of data and ensure the integrity of data is preserved by using DBMS keys.
By introducing an attribute as a key and making it compulsory , we can enforce that anything inserted in the table is identifiable and ensure data integrity (accuracy).
Types of keys in DBMS
- Super Key
- Primary Key
- Candidate Key
- Unique Key
- Secondary Key
- Composite Key
- Alternate Key
- Partial Key
- Foreign Key
1. Super Key - A super-key is a group of one or more attributes that, obtained altogether, permit us to uniquely identify a tuple (row) in the relation (table) . You can also say that the super key is the default key.
2. Primary Key - A primary key is a candidate key which is chosen by the database designer as the principal means of determining (identify) tuples (rows) within a relation (table) .
A key (whether primary, candidate, or super) is a substance (holdings) of the whole complete table (relation) , rather than of the particular tuple.
Primary key doesn't allow the tuples to have the same value on the key attribute at the same time. The identification of a key represents a condition in the real-world enterprise being modeled.
Things to remember while choosing a primary key-
Primary keys must be chosen with responsibility. As discussed , the name of a person choosing a primary key is obviously not sufficient, because there may be many people with the same name.
So we must have to choose unique values like we can choose a person's I'd number or email as the primary key. A primary key cannot accept a null value.
3. Candidate Key - A candidate key is a group or attributes (field) or attribute which uniquely identifies the tuples (rows) in a table (relation) .
As everybody knows that the primary key is a minimal of super key, for this reason there is one and only one primary key is in any table or we say that relationship though in a table there is more than one candidate key can take place.
And the database designer can choose any one of the candidate keys as their primary key in the relation.
4. Unique Key - A unique key is a group of one or more than one attributes(fields) of a table that uniquely identify data in a database table.
Could you possibly think it is few like a primary key but the basic difference between primary key and unique is that primary key cannot hold null value or record but unique key can hold null value but it cannot hold duplicate values.
And unique keys are also used as a foreign keys in other relations (table).
5. Secondary Key - A secondary key is a that candidate key which is being able to be considered as primary key ,but that has not been selected to be the primary key.
For this reason, a candidate key not preferred as a primary key is called secondary keys.
6. Composite Key - A composite key is a candidate key that be made up of two or more attributes (columns) combined. Uniquely identify an entity existence is known as composite key.
7. Alternate key - A alternate key is a group of attributes in a relation which can uniquely identify every row in that relation (table).
A table may have a lot of choices for primary key but we all know that in a table there is only one primary key are selected. All the key which is not selected as primary key is known as alternate key.
8. Partial key - A partial key is a group of columns that are applied to uniquely identify a weak entity group is known as partial key.
Partial key is simply a member of the key as only a subset columns can be identified as using it.
9. Foreign Key - As we know that one of the essential thing in database is creating relationships between database tables.
These relationship give a technique for reference data stored in a numerous tables, in such a way that user can retrieve records (data) in an efficient manner and than authenticate the occurrence of one table in the other table.
For the purpose of create a link between two tables believe that it is necessary to specify a foreign key in one table that reference attribute (column/field) in other table is primary key attribute.
We need two tables for the connection with each other and those tables have a common column and those column should be of same data types.
There is a parent child relationship followed, like a primary table is considered as parent table and foreign key table is considered as child table.
Conclusion
Mostly database contains only primary key , foreign key and unique key and left all keys are just a concept in database.
Primary key is considered as the important key of table because using primary key make data more highly reliable. Only points to be remembered are primary key does not contain and duplicate value and null values.