Understanding the MySQL Foreign key

A MySQL Foreign key is a field or multiple fields in a table that identify to other another table uniquely to enforce data integrity.

This relationship can also be seen as a parent and child.

Referential actions can be set to cascade down so that no child is left without its “parent” thus being redundant data.

It is good practice to use the primary key of the parent table as a foreign key. A foreign key constraint is defined on the child table.

Here is a diagram of a mock database with foreign keys:

Relationships

Starting at the bottom an image location must be in the locations table, its device must be in the device table and so forth.

This is the link a foreign key brings, in a way it puts a limit on what that value can be in the child table.

A user can have many locations and many images whilst an image only has one user and one location.

A device can have many images but an image only has one device (camera/phone etc.).

Put simply:

An image has a one to one relationship with the locations table.

A location has a one to many relationship with the images table.

These relationships give guidance on foreign key placements, In a one to many the child is the many. With one to one the child is the table that cannot function without the other.

ON DELETE

With an ON DELETE cascade foreign key constraint if a user gets deleted so does their locations and images. As it cascades downwards like a waterfall. If an image gets deleted only the exif data is deleted if there is an ON DELETE cascade.

If a location gets deleted then so do its images and any child sub-locations.

Using ON DELETE SET NULL will set the child foreign key field as null if the parent gets deleted.

ON UPDATE is another action triggered by when the parent value row gets updated.

Syntax

CREATE TABLE hobbies
(
    id    INT AUTO_INCREMENT PRIMARY KEY,
    hobby VARCHAR(255) NOT NULL
) ENGINE = INNODB;

CREATE TABLE users
(
    id       INT AUTO_INCREMENT PRIMARY KEY,
    username varchar(64) NOT NULL,
    hobby_id INT,
    CONSTRAINT fk_hobby
        FOREIGN KEY (hobby_id)
            REFERENCES hobbies (id, hobby)
            ON DELETE CASCADE
) ENGINE = INNODB;