- In a table, a key is a single field or a set of fields. It’s utilized to get or get records/data rows from a data table based on a condition or requirement.
- Keys can also be used to link together distinct database tables or views.
- It’s used to identify any record or row of data in the database in a unique way.
- It’s also used to construct and identify table relationships.
Types of Keys:
- Primary Key
- Candidate Key
- Super Key
- Foreign Key
- Alternate Key
- Composite Key
- Artificial Key
- It is the initial key used to uniquely identify a single instance of an entity.
- As we saw in the PERSON table, an entity can have many keys.
- The most appropriate key from those lists becomes the primary key.
- An characteristic or group of attributes that can uniquely identify a tuple is known as a candidate key.
- The remaining properties, with the exception of the primary key, are considered candidate keys. Candidates have the same strength as the primary key.
- A super key is a collection of attributes that can be used to uniquely identify a tuple. A candidate key is a superset of a super key.
- The column in a table that points to the primary key of another table is known as a foreign key.
- Every employee in a corporation is assigned to a specific department, and employee and department are two distinct entities. As a result, the department’s information cannot be stored in the employee table. That’s why the primary key of one table is used to connect these two tables.
- Department Id, the DEPARTMENT table’s primary key, is added to the EMPLOYEE table as a new attribute.
- Each tuple in a relation can be uniquely identified by one or more qualities, or by a combination of attributes.
- The candidate keys are these attributes or combinations of traits.
- From these candidate keys, one is chosen as the primary key, and the remaining candidate key, if it exists, is referred to as the alternate key.
- The total number of alternate keys is equal to the total number of candidate keys minus the primary key.
- It’s possible that an alternate key exists, but it’s also possible that it doesn’t.
- There is no alternate key in a relation if there is only one candidate key.
- A composite key is defined as a main key that has more than one characteristic.
- Concatenated Key is another name for this key.
- Artificial keys are keys that are constructed using data that is randomly assigned.
- When a primary key is huge and complex and has no relationships with many additional relations, these keys are formed.
- In most cases, the data values of fake keys are numbered sequentially.