Primary key vs foreign key

What is the difference between primary and foreign keys in relational databases?

Primary keys serve as the identifier of a unique record in a database table. It can be unique information, such as a driver’s license number, telephone number including area code, car VIN number, etc.

CREATE TABLE `ecommerce`.`customer` ( 
   `driving_license` VARCHAR(100),
   `name` VARCHAR(150) NOT NULL,
   `surname` VARCHAR(150) NOT NULL,
   `email` VARCHAR(150),
   `phone` VARCHAR(200),
    PRIMARY KEY `Primary key`(
   `driving_license`
    )
)
 ENGINE = InnoDB;

Multi-column primary keys

A primary key can contain one or more columns. There can be only one primary key in a table, even if it is composed of multiple columns. Example of a primary key that contains multiple columns:

Unique identifiers, IDs and auto-increment

In many cases, a primary key is a unique number that is either created outside the database system and stored in the database or is generated directly when the record is stored in the database table. This is done, for example, by the AUTO_INCREMENT column property in MySQL or MariaDB databases.

Foreign keys

A foreign key is defined by a column or more columns that point to the primary key of another table. There can be multiple foreign keys in a table, depending on the relationship that exists between the tables. In relational databases, a foreign key can be defined using a foreign key constraint, which guarantees data integrity between the child and the parent tables. By setting a foreign key constraint, you can ensure that the data in a given column in a child table matches the records in a column in the parent table.

The following example shows the contact_id column in the table Customer. The generated SQL script shows the foreign key definition and the reference to the id column in table Contact.

Example of a structure that allows storing records for customers, more than one email or phone number per customer and storing more than one address per contact.

An alternate design might be similar to the one displayed on the following screenshot:

JSON structure and embedded records

Another way to store data in cases similar to the example mentioned above is to store everything in a column that is of data type JSON. It will not be necessary to store data in other tables and create foreign keys. Whether or not this is the right way depends on many factors, and it is not possible to say whether one approach is always more correct than the other.

Thanks for reading!