A Key field also defines searches. Keys are used to establishing the relationship among tables, and they are helpful to enforce data integrity.
Types of Keys
1. Primary Key
- In a table the attribute or combination of columns that uniquely identify all possible rows in a table.
- A table can have only one primary key.
- A primary key cannot be null.
- Each value in the crucial primary attribute must be unique.
- The primary key cannot be changed or removed during database operations.
- A primary key is identified by underline the attribute name in the relation.
- STUDENT (ROLLNO, NAME, GENDER, ADDRESS, TELL), Example: A Social Security number (associated with a specific person), ISBN (associated with a particular book) student roll number (associated with only one student in a class).
2. Secondary Key
Secondary essential May or may not be a unique field. Some times records are required to access by an area other than the primary key. In these situations, another key that is used is called a secondary key.
3. Candidate Key/Alternate Key
A table can have more than one attribute to be qualified as a primary key. One of them is selected as the primary key, and the remaining keys are called candidate keys.
Sometimes a relation has more than one attribute that can be used as the primary key. In these situations, it is difficult to select a primary key. There can be more than one unique field or combinations of individual areas in a relation. These keys are termed as candidate keys or alternate keys.
4. Composite/Concatenate Key
It is created in a situation when no single field fulfills the property of uniqueness. To make different more than one area is combined and used as a primary key.
5. Sort/Control Key
A field or a set of fields in a record that dictate the sequence of the file according to our requirement. Example: The sort keys STATE and NAME arrange the table data alphabetically by name within the state. STATE is the primary sort kev., and NAME is the minor sort key.
6. Foreign Key
- The table that contains a foreign key is called the dependent table or child table.
- The table from where the foreign key is referred to is called the parent table or independent table.
- The columns in the parent table must form a primary key or unique key.
- The values in one row of the child table columns must occur in a single row in the parent table.
- A row in the child table cannot contain values that don’t exist in the parent table.
- Foreign key and primary key are used to establish the relationship between two tables.
- It is also helpful to maintain data integrity.
In the following example, the teacher table is the parent table, and student table is the child table. In the parent table, TEACHER_CODE is the primary key which is referenced in the STUDENT table as a foreign key.