Defining Relationships Between Tables in MySQL

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,