H2 Database - Create

Donald Green

Feb 3, 2022 - 02:02

0 37

CREATE is a generic SQL command used to create tables, schemas, sequences, views, and users in the H2 database server.

Create table

Create Table is a command used to create a custom table in the current database.

Syntax

Following is the general syntax of the "Create Table" command.

CREATE [ CACHED | MEMORY] [TEMP | [ GLOBAL | LOCAL] TEMPORARY]
TABLE [ IF NOT EXISTS ] name
[ ( { columnDefinition | constraint } [,...] ) ]
[ ENGINE tableEngineName [ WITH tableEngineParamName [,...] ] ]
[ NOT PERSISTENT ] [ TRANSACTIONAL ]
[ AS select ]

Using the general syntax of the "Create Table" command, we can create different types of tables such as cached tables, memory tables, and temporary tables. Below is a list to describe the various sentences from this syntax.

CACHED - Cached tables are the default type for regular tables. This means that the number of rows is not limited by main memory.
MEMORY - Memory tables are the default type for temporary tables. This means that the memory tables should not get too large and the index data is kept in main memory.
TEMPORARY - Temporary tables are deleted when the database is closed or opened. Basically, temporary tables are of two types:
- Type GLOBAL - Available for all connections.
- LOCAL type - available on the current connection.
The default type for temporary tables is the global type. Temporary table indexes are stored in main memory unless the temporary table is created using CREATE CACHED TABLE.
ENGINE - The ENGINE option is required only when using custom table implementations.
NOT PERSISTENT is a modifier to keep the complete table data in memory and all rows are lost when the database is closed.
TRANSACTIONAL is a keyword that commits an open transaction and this command only supports temporary tables.

CACHED - Cached tables are the default type for regular tables. This means that the number of rows is not limited by main memory.

MEMORY - Memory tables are the default type for temporary tables. This means that the memory tables should not get too large and the index data is kept in main memory.

TEMPORARY - Temporary tables are deleted when the database is closed or opened. Basically, temporary tables are of two types:

Type GLOBAL - Available for all connections.

LOCAL type - available on the current connection.

The default type for temporary tables is the global type. Temporary table indexes are stored in main memory unless the temporary table is created using CREATE CACHED TABLE.

ENGINE - The ENGINE option is required only when using custom table implementations.

NOT PERSISTENT is a modifier to keep the complete table data in memory and all rows are lost when the database is closed.

TRANSACTIONAL is a keyword that commits an open transaction and this command only supports temporary tables.

example

In this example, let's create a table named tutorials_tbl using the following data.

Sr.No	Column name	Data type
one	I'D	int
2	title	varchar(50)
3	author	VARCHAR (20)
4	date of application	date

The following query is used to create the tutorials_tbl table along with the column data.

CREATE TABLE tutorials_tbl(
   id INT NOT NULL,
   title VARCHAR(50) NOT NULL,
   author VARCHAR(20) NOT NULL,
   submission_date DATE,
);

The above query results in the following output.

(0) rows effected

Create schema

Create Schema is a command used to create a user-specific schema with a specific authorization (under the currently logged in user).

Syntax

Following is the general syntax of the Create Schema command.

CREATE SCHEMA [ IF NOT EXISTS ] name [ AUTHORIZATION ownerUserName ]

In the above general syntax, AUTHORIZATION is the keyword used to provide the corresponding username. This command is optional, which means that if we don't provide a username, it will take into account the current user. The user who runs the command must have administrator rights as well as owner.

This command commits an open transaction on this connection.

example

In this example, let's create a schema named test_schema under user SA using the following command.

CREATE SCHEMA test_schema AUTHORIZATION sa ;

The above command produces the following output.

(0) rows effected

Create Sequence

Sequence is a concept that is used to generate a number by following a sequence for an id or any random values of a column.

Syntax

Following is the general syntax of the create sequence command.

CREATE SEQUENCE [ IF NOT EXISTS ] newSequenceName [ START WITH long ]
[ INCREMENT BY long ]
[ MINVALUE long | NOMINATE | NO MINVALUE]
[ MAXVALUE long | NOMAXVALUE | NO MAXVALUE]
[ CYCLE long | NOCYCLES | NO CYCLE]
[ CACHE long | NOCACHE | NO CACHE]

This general syntax is used to create a sequence. The sequence data type is BIGINT . In this sequence, the values are never reused, even if the transaction is rolled back.