Creating tables

Meaningful creation of tables

In the previous lectures, we already got a little acquainted with queries for creating tables, now it's time to delve deeper into this.

Creating a table is very similar to declaring a class in Java and has this pattern:

CREATE TABLE table_name (
	column1 datatype,
	column2 datatype,
	column3 datatype,
   ....
);

For example, let's write a query that creates a table with users:

CREATE TABLE user (
	id INT,
	name VARCHAR(100),
	level INT,
	created_date DATE,
);

It looks very simple, because a lot of nuances are not indicated here.

First, the table may have additional settings.

And secondly, each column can have additional settings.

And we will start with the settings for creating columns.

Keys in the table

In addition to the name and data type, a table column can also have the following settings:

Below we will look at some of them.

First, it's PRIMARY KEY .

Most often, this is a separate column with the name id and the type INT. This is the so-called "master key" of the table, and all of its rows have unique values ​​for this key. It is primarily used so that other tables can refer to the records of our table and point to its specific record.

Second is UNIQUE KEY .

In some ways, it is similar to PRIMARY KEY, although its semantic load is completely different. If a column has the UNIQUE attribute, then all values ​​in that column must be unique. A good example of the difference between UNIQUE KEY and PRIMARY KEY is the list of people in the passport office.

The tax number is a PRIMARY KEY, it is used to make it easy to refer to the right person from other tables.

The passport number is a UNIQUE KEY. No two people can have the same passport number. However, the passport number can be changed. For example, when changing the surname. And the tax number will stay with you forever. This is the main task of PRIMARY KEY. An example of a query specifying the PRIMARY KEY:

CREATE TABLE user (
	id INT PRIMARY KEY,
	name VARCHAR(100),
	level INT,
	created_date DATE,
);

Data type settings

There are less important but useful attributes.

DEFAUL value

When inserting data (adding a new row) into a table, you can leave out the values ​​of some columns if they have a default value. In this case, the SQL server will simply set the value of the column itself.

Starting with version 8 of MySQL, you can specify an expression as a value.

NOT NULL

If you specify the NOT NULL attribute for a column when creating a table, then SQL Server will ensure that it is impossible to store a NULL value in this column. By default, any column can have a NULL value, even an INT column. Which is a bit non-obvious for a Java programmer.

AUTO_INCREMENT

It is usually used for column id's. When you add a new row to a table, you really want the SQL server to assign an id for this entry. Who better than the server knows how many rows he has in the table. Especially if requests from different clients go to the same SQL server.

This is exactly what the AUTO_INCREMENT attribute does. When adding a new row, we simply do not pass anything as an id, and SQL server will specify the correct ID for this record: it will simply take the ID of the last existing record and increase it by 1. This attribute can only be used with integer and real number types. And, of course, let's make an example of creating such a table:

CREATE TABLE user (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(100) NOT NULL,
	level INT DEFAULT 1,
	created_date DATE NOT NULL,
);

1

Task

Module 4. Working with databases, level 6, lesson 1

Locked

task0604

task0604

Open

1

Task

Module 4. Working with databases, level 6, lesson 1

Locked

task0605

task0605

Open