Before creating the table, you need to select the database to which the table will be written. This is done using the USE statement:
The CREATE TABLE statement is used to create the table. Its simplest use is as follows:
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.
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.
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:
Specifies a column or set of columns as the primary key.
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.
Indicates that the values in this column for all records must be different from each other.
Indicates that the values in this column must be different from NULL.
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:
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.
To view the description of the created table, you can use the operator DESCRIBE.
In addition to the description of the columns, when creating a table, you can additionally specify the following parameters:
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_1>, <column_n>) after enumerating the columns:MySQL
CREATE TABLE Users ( id INT, name VARCHAR(255) NOT NULL, age INT NOT NULL DEFAULT 18, PRIMARY KEY (id) );
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
CREATE TABLE Companies ( id INT, 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
CREATE TABLE Users ( id INT, name VARCHAR(255) NOT NULL, age INT NOT NULL DEFAULT 18, company INT, 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
FOREIGN KEY (<column_1>, <column_n>) REFERENCES <external_table> (<external_table_column_1>, <external_table_column_n>) [ON DELETE reference_option] [ON UPDATE reference_option]
The full query for creating a table with a foreign key will be as follows: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 determine the behavior of the current record when changing or deleting the record to which it refers.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 is done using the DROP TABLE operator.
DROP TABLE [IF EXIST] table_name;