/    /  MysQL – Key

MySQL – Key

 

k1

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

Primary 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.

Note:

The primary key of a table must be unique and must only contain one value.

Syntax:

CREATE TABLE <table_name>
(
Column_name1 datatype(),
Column_name2 datatype(),
PRIMARY KEY (Column_name1)
);

Candidate Key

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.

UNIQUE KEY

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.

Syntax:

CREATE TABLE <table_name>
(
Column_name1 datatype() UNIQUE,
Column_name2 datatype(),
);


FOREIGN KEY

A foreign key relationship exists between two database tables. According to these tables, three conditions must be met.

  1. There must be a match between the types of both tables in the database.
  2. Foreign key relationships require that these fields be indexed.
  3. Relationships between foreign key fields must be similar in data type.

Super Key

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.