In MySQL, the relationships between tables are defined through the use of matching columns referred to as primary keys and foreign keys.

They allow to uniquely identify each record in a table ensuring data integrity and referential integrity across the database.

Setting a primary key

A primary key is a non-null column or set of columns that uniquely identifies each record in a table.

Primary keys can essentially be of three types:

• A natural key is a single column based on a real-world observable, such as an email address, a social security number, or a phone number.

• A surrogate key is a single column based on a value specifically created to be used as a key within the boundaries of the database and that has no meaning in the real-world, such as a numerical identifier that is automatically incremented by the database itself whenever a new record is created.

• A composite key is a set of columns that when considered separately may not be unique, but when taken altogether are guaranteed to ensure uniqueness, such as the combination of a first name, a last name, and an address.

To define a column as a primary key of a table, you can use the PRIMARY KEY constraint:

CREATE TABLE table_name(  column_name column_type [...] PRIMARY KEY,