Data Relationships
- One-to-Many (1:N): This is the most fundamental and widely used relationship. A single record in one table (the “one” side) can be linked to multiple records in another table (the “many” side). This is often achieved through the use of a foreign key, which references the primary key of the “one” side table.
- Many-to-Many (N:N): Here, multiple records in one table can be associated with multiple records in another table. Relational databases cannot directly represent this relationship, but we can create a workaround using an associative table. This associative table has foreign keys referencing both the original tables and establishes the many-to-many connection.
- One-to-One (1:1): In this relationship, a single record in one table corresponds to exactly one record in another table. This is less common but can be used in specific scenarios.
| SQL term | Relational database term | Description |
|---|---|---|
| Row | Tuple or record | A data set representing a single item |
| Column | Attribute or field | A labeled element of a tuple, e.g. «Address» or «Date of birth» |
| Table | Relation or Base relvar | A set of tuples sharing the same attributes; a set of columns and rows |
| View or result set | Derived relvar | Any set of tuples; a data report from the RDBMS in response to a query |
MySQL primary key
In MySQL, a primary key is a column or a set of columns that uniquely identifies each row in the table. A primary key column must contain unique values.
If the primary key consists of multiple columns, the combination of values in these columns must be unique. Additionally, a primary key column cannot contain NULL.
MySQL foreign key
In this diagram, each customer can have zero or many orders and each order belongs to one customer.
The relationship between customers table and orders table is one-to-many.
A table can have more than one foreign key where each foreign key references a primary key of the different parent tables.
Once a foreign key constraint is in place, the foreign key columns from the child table must have the corresponding row in the parent key columns of the parent table, or values in these foreign key columns must be NULL (see the SET NULL action example below)
Multiple rows in the orders table can have the same customerNumber
Self-referencing foreign key
Sometimes, the child and parent tables may refer to the same table. In this case, the foreign key references back to the primary key within the same table.
The reportTo column is a foreign key that refers to the employeeNumber column which is the primary key of the employees table.
This relationship allows the employees table to store the reporting structure between employees and managers. Each employee reports to zero or one employee and an employee can have zero or many subordinates.
The foreign key on the column reportTo is known as a recursive or self-referencing foreign key.