MySQL – Key
The term “key” refers to a data item in MySQL that uniquely identifies a record. Another way of describing a key is that it is a collection of columns that uniquely identify a record in a database. In this case, it is used to retrieve or extract rows from a table as needed. There are many types of constraints associated with keys, such as columns that cannot contain repeated values or null values.
Types of Keys
- Super Key
- Candidate Key
- Primary Key
- Unique Key
- Foreign Key
Each row in the table is represented by a primary key column or set of columns. In accordance with Wikipedia, a primary key is a unique combination of attributes that uniquely identifies a tuple (row) in a relationship.
The primary key of a table must be unique and must only contain one value.
CREATE TABLE <table_name> ( Column_name1 datatype(), Column_name2 datatype(), PRIMARY KEY (Column_name1) );
It is known as a candidate key in MySQL Keys when a characteristic or group of attributes is used to uniquely identify a tuple.
The remaining properties, except for the primary key, are considered candidate keys. A candidate key has the same strength as a primary key.
As an example, consider the “Employee” table. In this table, Emp_Id, Emp_Number, and Emp_Name are the three attributes. While Emp_Id, Emp_Number, and Emp_Name should have unique values, Emp_Name may contain duplicate values if multiple employees have the same name.
[Emp Id] and [Emp Number] are the candidate keys here.
It is used to modify the structure of the table and to add a unique key for the specified column. One of the null values may be inserted in the column with a unique key, and the unique key table may contain more than one unique key. A column constraint is involved.
CREATE TABLE <table_name> ( Column_name1 datatype() UNIQUE, Column_name2 datatype(), );
A foreign key relationship exists between two database tables. According to these tables, three conditions must be met.
- There must be a match between the types of both tables in the database.
- Foreign key relationships require that these fields be indexed.
- Relationships between foreign key fields must be similar in data type.
Relational databases define a Super Key as a set of attributes that can identify every tuple within a relation. Because Super Key values are unique, tuples with the same Super Key value should also have the same non-key attributes.
Taking the following EMPLOYEE database as an example (EMPLOYEE ID, EMPLOYEE NAME), two employees’ names may be the same, but their EMPLOYEE IDs cannot. As a result, this combination may be crucial.