Create a table

This document describes how to use SQL to create tables. The previous document introduces creating a database named modatabase. In this document, we introduce creating a table in this database.

Note

Here only a brief description of the CREATE TABLE statement. For more information, see CREATE TABLE.

Prepare before starting

Before reading this page, you need to prepare the following:

• Create instance has been completed.
• Understand what database schema is.
• Created a database.

What is a table

A table is a logical object in a MatrixOne Intelligence database that belongs to a database and is used to save data.

A table organizes data records in the form of rows and columns, and a table has at least one column. If n columns are defined in the table, each row of data will have fields that are exactly the same as the data format in these n columns.

Name table

Create a table name with practical meaning, with table names containing keywords or numbering specifications, and follow the naming specifications for easy search and use.

The CREATE TABLE statement usually takes the following form:

CREATE TABLE {table_name} ({elements});

Parameter description

• {table_name}: table name.
• {elements}: Comma-separated list of table elements, such as column definitions, primary key definitions, etc.

Define columns

Columns are subordinate to tables, and each table has at least one column. Columns provide a structure for the table by dividing the values ​​in each row into small units of a single data type.

Column definitions usually use the following form:

{column_name} {data_type} {column_qualification}

Parameter description

• {column_name}: Column name.
• {data_type}: The data type of the column.
• {column_qualification}: Column qualification.

Here we introduce the creation of a table named NATION to store user information in the modatabase library.

You can add some columns to the NATION table.

CREATE TABLE NATION(
N_NATIONKEY INTEGER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152)
);

Example explanation

The following table explains the fields in the above example:

Field Name	Data Type	Function	Interpretation
N_NATIONKEY	INTEGER	Unique Identification of Ethnicity	All Identities should be of INTEGER type
N_NAME	CHAR	Ethnic Names	Ethnic names are all char types and no more than 25 characters
N_REGIONKEY	INTEGER	Regional code, unique identifier	All identifiers should be of type INTEGER
N_COMMENT	VARCHAR	comment information	varchar type, no more than 152 characters
MatrixOne Intelligence supports many other column data types, including integers, floating point numbers, time, etc. See [Data Type] (../../Reference/Data-Types/data-types.md).

Create a complex table

Create a ORDERS table.

CREATE TABLE ORDERS(
O_ORDERKEY BIGINT NOT NULL,
O_CUSTKEY INTEGER NOT NULL,
O_ORDERSTATUS CHAR(1) NOT NULL,
O_TOTALPRICE DECIMAL(15,2) NOT NULL,
O_ORDERDATE DATE NOT NULL,
O_ORDERPRIORITY CHAR(15) NOT NULL,
O_CLERK CHAR(15) NOT NULL,
O_SHIPPRIORITY INTEGER NOT NULL,
O_COMMENT VARCHAR(79) NOT NULL,
PRIMARY KEY (O_ORDERKEY)
);

This table contains more data types than the NATION table:

Field Name	Data Type	Function	Interpretation
O_TOTALPRICE	DECIMAL	Used to mark price	The accuracy is 15 and the proportion is 2, that is, the accuracy represents the total number of digits of the field value, and the proportion represents how many digits are after the decimal point. For example: decimal(5,2), that is, when the accuracy is 5 and the proportion is 2, its value range is -999.99 to 999.99. decimal(6,1) , that is, when the accuracy is 6 and the ratio is 1, its value range is -99999.9 to 99999.9.
O_ORDERDATE	DATE	Date Value	Date of Order Generation

Select the primary key

A primary key is one or a group of columns, and this value combined by all primary key columns is the unique identifier of the data row.

The primary key is defined in the CREATE TABLE statement. Primary key constraints require that all constrained columns contain only non-NULL values.

A table can have no primary key, and the primary key can also be of a non-integer type.

Add column constraints

In addition to primary key constraints, MatrixOne Intelligence also supports other column constraints, such as: non-null constraint NOT NULL, default value DEFAULT, etc.

Fill in default value

To set the default value on the column, use the DEFAULT constraint. The default value will allow you to insert data without specifying the value of each column.

You can use DEFAULT with supported SQL functions to move the calculation of default values ​​out of the application layer, saving the application layer's resources. Here is a simple example, you can use the following statement:

create table t1(a int default (1), b int);
insert into t1(b) values(1), (1);
> select * from t1;
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 1 | 1 |
+------+------+
2 rows in set (0.01 sec)

As you can see, the default value of a is 1.

You can also set the default value to the time when the value is inserted, refer to the following simple example:

-- Create a table and set the default value to the current time
CREATE TABLE t2 (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    created_at DATETIME DEFAULT NOW()
);

INSERT INTO t2 (id, name) VALUES
(1, 'John'),
(2, 'Jane'),
(3, 'Mike');

> SELECT * FROM t2;
+------+-----------------------------------------------------------------------------------------------------------------------
| id | name | created_at |
+------+-----------------------------------------------------------------------------------------------------------------------
| 1 | John | 2023-07-10 11:57:27 |
| 2 | Jane | 2023-07-10 11:57:27 |
| 3 | Mike | 2023-07-10 11:57:27 |
+------+-----------------------------------------------------------------------------------------------------------------------
3 rows in set (0.00 sec)

After executing the above insert statement, the created_at column of each row will be automatically set to the current time.

Prevent duplication

If you need to prevent duplicate values ​​from appearing in columns, you can use the UNIQUE constraint.

For example, you need to make sure that the values ​​of the national tag are unique, and you can override the creation of the NATION table in SQL like this:

CREATE TABLE NATION(
N_NATIONKEY INTEGER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152),
UNIQUE KEY (N_NATIONKEY)
);

If you try to insert the same N_NATIONKEY in the NATION table, an error will be returned.

Prevent null values

If you need to prevent null values ​​from appearing in columns, you can use the NOT NULL constraint.It is better to use ethnic names to give an example. In addition to the unique value of ethnic tags, we also hope that ethnic names cannot be empty. So we can write the creation of NATION table here SQL:

CREATE TABLE NATION(
N_NATIONKEY INTEGER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152),
PRIMARY KEY (N_NATIONKEY)
);

Execute the SHOW TABLES statement

You need to view all tables under the modatabase database, and you can use the SHOW TABLES statement:

SHOW TABLES IN `modatabase`;

The running result is:

+--------------------------+
| tables_in_modatabase |
+--------------------------+
| nation |
| orders |
+--------------------------+

Rules to follow when creating tables

The rules that should be followed when naming tables

• Use fully qualified table names (for example: CREATE TABLE {database_name}.{table_name}). This is because when you do not specify a database name, MatrixOne Intelligence will use the current database in your SQL session. If you do not specify the database using USE {databasename}; in your SQL session, MatrixOne Intelligence will return an error.

• Please use meaningful table names. For example, if you need to create a user table, you can use the name: user, t_user, users, etc., or follow the naming specifications of your company or organization. If your company or organization does not have corresponding naming specifications, you can refer to the table naming specifications.

• Multiple words are underscore-separated, and more than 32 characters are not recommended.

• The forms of different business modules are created separately DATABASE and the corresponding comments are added.

The rules to follow when defining columns

• View the data types of supported columns.
• Check the rules you should follow when selecting a primary key and decide whether to use the primary key column.
• Check the Add Column Constraint to decide whether to add the constraint to the column.
• Please use meaningful column names and recommend that you follow the company or organization's table naming specifications. If your company or organization does not have corresponding naming specifications, you can refer to the column naming specifications.

The rules that should be followed when selecting a primary key

• Define a primary key or unique index within the table.
• Try to select meaningful columns as primary keys.
• For performance considerations, try to avoid storing ultra-wide tables. The number of table fields should not exceed 60. It is recommended that the total data size of a single row should not exceed 64K. It is best to disassemble the fields with too large data length to another table.
• Complex data types are not recommended.
• JOIN fields are required, and the data types are absolutely consistent and avoid implicit conversions.
• Avoid defining primary keys on a single monotonic data column. If you use a single monotonic data column (for example, AUTO_INCREMENT column) to define a primary key, it may have a negative impact on write performance.