Creating and deleting tables

Creating a table

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

MySQL
USE database_name;

The CREATE TABLE statement is used to create the table. Its simplest use is as follows:

MySQL
CREATE TABLE [IF NOT EXIST] table_name (
     column_1 data type,
    [column_2 data type,]
    ...
    [column_n data type,]
);

For example, let's create a table of users.

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

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

Additional column definition options

The above definition of columns in a table is simplified. In addition to the name of the column and its type, it is sometimes necessary to add the following optional parameters to the definition:

• PRIMARY KEY Specifies a column or set of columns as the primary key.

• 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.

• UNIQUE Indicates that the values in this column for all records must be different from each other.

• NOT NULL Indicates that the values in this column must be different from NULL.

• DEFAULT Specifies the default value. This parameter does not apply to the BLOB, TEXT, GEOMETRY and JSON.

For our table of users, you can specify the following parameters:

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

So in this example

• id - a numeric field that is the primary key;
• name - a string type field with a maximum length of 255 characters, which is mandatory;
• age - a numeric field with a default value of 18.

Description of the table

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

MySQL
DESCRIBE Users;

Additional table definition options

In addition to the description of the columns, when creating a table, you can additionally specify the following parameters:

• Primary key If you have not defined the primary key using the parameters to the column, then this can be done using the parameters to the table.

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

• Foreign keys Add the field company to the Users table - the place of work of our user, which will refer to the record in the Companies table.

  MySQL
  CREATE TABLE Users (
      id INT,
      name VARCHAR(255) NOT NULL,
      age INT NOT NULL DEFAULT 18,
      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:

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

  If you have foreign keys, you can define the behavior of the current record when a record is changed or deleted, by which we link.

  MySQL
  CREATE TABLE Users (
      id INT,
      name VARCHAR(255) NOT NULL,
      age INT NOT NULL DEFAULT 18,
      company INT,
      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
  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.

Deleting a table

Deleting a table is done using the DROP TABLE operator.

MySQL
DROP TABLE [IF EXIST] table_name;