Table creation

We explain how to create tables from SQL statements. We define the types of main fields and the way to specify the indices.

In general, most databases have powerful database editors that allow the quick and easy creation of any type of table with any type of format.

However, once the database is hosted on the server, it may be the case that we want to introduce a new table either temporarily (to manage a shopping cart, for example) or permanently due to the specific needs of our application. .

In these cases, we can, from an SQL sentence, create the table with the format we want, which can save us more than one headache.

These types of statements are especially useful for databases like Mysql, which work directly with SQL commands and not through editors.

To create a table we must specify various data: The name we want to assign to it, the names of the fields and their characteristics. In addition, it may be necessary to specify which of these fields are to be indexes and of what type they are to be.

The creation syntax may vary slightly from database to database as the accepted field types are not completely standardized.

Below we briefly explain the syntax of this sentence and we propose a series of practical examples:

Syntax

Create Table table_name

(

field_name_1 type_1

field_name_2 type_2

field_name_n type_n

Key(field_x,…)

)

Now let’s take as an example the creation of the orders table that we have used in previous chapters:

Create Table orders

(

order_id INT(4) NOT NULL AUTO_INCREMENT,

customer_id INT(4) NOT NULL,

item_id INT(4)NOT NULL,

date DATE,

quantity INT(4),

total INT(4), KEY(order_id, customer_id, item_id)

)

In this case we create the id fields which are considered to be of an integer type of a length specified by the number in parentheses. For id_pedido we require that said field be automatically increased (AUTO_INCREMENT) by one unit each time a new record is introduced in order to automate its creation. On the other hand, to avoid an error message, it is necessary to require that the fields that are going to be defined as indexes cannot be null (NOT NULL).

The date field is stored in date format (DATE) to allow its correct exploitation from the functions provided for this purpose.

Finally, we define the indices by listing them in parentheses preceded by the word KEY or INDEX.

In the same way we could create the articles table with a statement like this:

Create Table items

(

item_id INT(4) NOT NULL AUTO_INCREMENT,

title VARCHAR(50),

author VARCHAR(25),

editorial VARCHAR(25),

real price,

KEY(id_article)

)

In this case it can be seen that the alphanumeric fields are entered in the same way as the numeric ones. We remember once again that in tables that have common fields it is vitally important to define these fields in the same way for the proper functioning of the database.

There are many options offered when generating tables. We are not going to treat them in detail as it is out of the strictly practical. We will only show some of the types of fields that can be used in the creation of tables with their characteristics:

Type Bytes Description INT or INTEGER 4 Integers. There are other types of greater or lesser length specific to each database. DOUBLE or REAL 8 Real numbers (large and with decimals). They allow to store all kinds of non-integer number. CHAR 1/character Predefined fixed-length alphanumeric VARCHAR 1/character+1 Variable-length alphanumeric DATE 3 Dates, there are multiple specific formats for each database BLOB 1/character+2 Non-indexable large texts BIT or BOOLEAN 1 Stores one bit of information (true or false)