Creating and deleting tables

Before creating the table, you need to select the database to which the table will be written. This is done using the USE statement:

USE database_name;

MySQL 8.4
CREATE TABLE Users (
    id INTEGER,
    name VARCHAR(255),
    age INTEGER
);

INTEGER, VARCHAR(255) - data types: numeric and string, respectively. More details about them can be found in the following articles.

• AUTO_INCREMENT

  Indicates that the value of this column will be automatically increased when new records are added to the table. Each table has a maximum of one AUTO_INCREMENT column. It is worth noting that this parameter can only be applied to integer and floating point types.

This parameter does not apply to the BLOB, TEXT, GEOMETRY and JSON types.

MySQL 8.4
CREATE TABLE Users (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL DEFAULT 18
);

• id - a numeric field that is the primary key with auto-increment;

MySQL 8.4
CREATE TABLE Users (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

To view the description of the created table, you can use the operator DESCRIBE.

MySQL 8.4
DESCRIBE Users;

Primary key

If you have not defined the primary key using the column parameters, then you can do this using additional table parameters by adding the entry PRIMARY KEY (column [, ...]) after enumerating the columns:

MySQL 8.4
CREATE TABLE Users (
    id INTEGER,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL DEFAULT 18,
    PRIMARY KEY (id)
);

Foreign keys

Suppose we want to store data about the company our users work for. Let's create a small table Companies in which we will store a unique identifier and the name of the company:

MySQL 8.4
CREATE TABLE Companies (
    id INTEGER,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

Next, you need to add the company field to the Users table – the place of work of our user, which will refer to the entry in the Companies table. The full query for creating a table will look like this:

MySQL 8.4
CREATE TABLE Users (
    id INTEGER,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL DEFAULT 18,
    company INTEGER,
    PRIMARY KEY (id)
);

A foreign key is used to ensure that the company column contains an identifier that exists in the Companies table when new entries are added to the Users table. It has the following syntax:

FOREIGN KEY (column [, ...])
REFERENCES external_table (external_table_column [, ...])
[ON DELETE action]
[ON UPDATE action]

The full query for creating a table with a foreign key will be as follows:

MySQL 8.4
CREATE TABLE Users (
    id INTEGER,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL DEFAULT 18,
    company INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY (company) REFERENCES Companies (id)
);

If you have foreign keys, you can determine the behavior of the current record when changing or deleting the record to which it refers.

MySQL 8.4
CREATE TABLE Users (
    id INTEGER,
    name VARCHAR(255) NOT NULL,
    age INTEGER NOT NULL DEFAULT 18,
    company INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY (company) REFERENCES Companies (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

ON DELETE RESTRICT means that if you try to delete a company that has data in the Users table, the database won't let you do this:

MySQL 8.4
Cannot delete or update a parent row: a foreign key constraint fails

If ON DELETE CASCADE was specified, then when deleting the company, all users would be deleted, referring to this company.

There is one more option - ON DELETE SET NULL. When used, the database will write NULL as the value of the company field for all users who worked at the remote company.

ON UPDATE CASCADE means that if a company changes its ID, then all Users will get a new ID in the company field.