SQL Data Definition

Supported functionality related to Data Definition Language of SQL.

Syntax is case insensitive following this template:

KEYWORDS required_input [optional_input];

Database basics

CREATE DATABASE

Since version: 1.4 Creates database with specified name and block size. Database name cannot contain characters '/' (slash), '\' (backslash) or '@' (at). All tables within the same database has the same block size. For more information about block size see Hardware limitations section. If no block size is specified, the value specified in configuration file will be used.

CREATE DATABASE database_name [block_size];

ALTER DATABASE

Since version: 1.4.1 Allows you to modify existing database. You can rename database using the following command:

ALTER DATABASE database_name RENAME TO new_database_name;

DROP DATABASE

Since version: 1.4.1 Deletes the chosen database.

DROP DATABASE database_name;

Table Basics

Constraints Types

Each constraint is binded with constraint type, constraint name and one or more column names. So we can have for example a constraint UNIQUE which will be named "uniq_const" and it will be binded with two columns "colA" and "colB". If we decide to drop the constraint "uniq_const", it will drop this constraint on all columns which it is binded with (so in this case "colA" and "colB"). Adding duplicit constraint of the same type results in error. For example, you cannot create constraint NOT NULL with constraint name "cons_not_null_1", which would be binded with "ColA" and "ColB" and then create another constraint NOT NULL, let's name it "cons_not_null_2", which would be binded just with "ColB" (because the "ColB" already has NOT NULL constraint which is named "cons_not_null_1"). If you want to drop a constraint, you have to use it's name.

UNIQUE

Since version: 1.4.2 The UNIQUE constraint ensures that all values in a constrained column are different and in this column cannot be a NULL VALUE, not even a single one. To add UNIQUE constraint on a column (using ALTER TABLE command), this column must have NOT NULL constrained already applied on it, otherwise it results in error. When trying to add UNIQUE constraint on a column which have duplicate values or a NULL value/s, it results in error. Dropping UNIQUE constraints does not drop also NOT NULL constraint, NOT NULL constraint will remain. The suffix in default constraint name is: "_UC" (the default constraint name is made up of the column name and constraint suffix, for more information see create table command). If the default name of the constraint has been used (because the constraint name has not been explicitly defined), this default name must be used when dropping constraint which name was not explicitly defined.

NOT NULL

Since version: 1.4.2 The NOT NULL constraint ensures that the constrained column cannot contains NULL values. When trying to add NOT NULL constraint (using ALTER TABLE command) on a column which has one or more NULL values, it results in error. You cannot drop NOT NULL constraint if a column has UNIQUE constraint - you need to first drop UNIQUE constraint and then you can drop NOT NULL constraint. The suffix in default constraint name is: "_NC" (the default constraint name is made up of the column name and constraint suffix, for more information see create table command). If the default name of the constraint has been used (because the constraint name has not been explicitly defined), this default name must be used when dropping constraint which name was not explicitly defined.

CREATE TABLE

Since version: 1.4 Creates a new table along with its columns in the database. Table name cannot contain characters '/' (slash), '\' (backslash) or '@' (at). The block size can be specified for particular tables, but if the block size is not eplicitly defined, the value of the database block size will be used also as the table block size for this new table. You can also add constraint on one or more columns. Each constrained has it's name, type and list of columns that it aplies to. Adding constraints is optional and CONSTRAINT_TYPE can be chosen from this list. Optional parameter allows you to make an index. In order to run this command you need to select database first (see Console).

CREATE TABLE table_name [block_size]
[, CONSTRAINT_TYPE constraint1_name (column1_name [, column2_name, ...])
[, CONSTRAINT_TYPE constraint2_name (column2_name [, column1_name, ...]), ...]
]
(
column1_name COLUMN_DATA_TYPE
[, column2_name COLUMN_DATA_TYPE, ...]
[INDEX index_name (columnA_name, ...)]
);

We also support another syntax to create table with contraints, withou explicitly specifying their names. The default values for the constraint names would be used, which are made up of the column name and suffix according to constraint type (e.g. unique constraint for column named "colA" would look like this: "colA_UC"). The constraint suffixes are mentioned here under each constraint type. The syntax is following:

CREATE TABLE table_name [block_size]
(column1_name COLUMN_DATA_TYPE CONSTRAINT_TYPE
[, column2_name COLUMN_DATA_TYPE CONSTRAINT_TYPE, ...]
);

ALTER TABLE

Allows you to modify existing table.

Add Column

Since version: 1.4 You can add column with specified datatype. Column name cannot contain characters '/' (slash), '\' (backslash) or '@' (at). When adding new column to the table with data already inserted, new column will be fill with NULL data values to equalize the number of data in columns.

ALTER TABLE table_name ADD column_name datatype;

Change Column Data Type

Since version: 1.4 You can also change data type of a specified column in a specified table. When changing to to a data type with a smaller byte length, there is a possible lost of data. Also when changing data type is not possible for some elements of a particular column (e.g. cast string 'DHC-05' to integer), the result of changed data type for these elements is NULL value. We recommend you to check the values in a column before changing data type so the result is not the column with all elements as a NULL value. To see, what data types we support see Supported Data Types. The possible combinations for changing data types are:

  • Bool → Bool, Double, Float, Int, Long, String

  • Double → Bool, Double, Float, Int, Long, String

  • Float → Bool, Double, Float, Int, Long, String

  • Int→ Bool, Double, Float, Int, Long, String

  • Long → Bool, Double, Float, Int, Long, String

  • String → Bool, Double, Float, Geo_Point, Geo_Polygon, Int, Long, String

  • Geo_Point → String

  • Geo_Polygon → String

Since version: 1.4.2 When corventing String to Bool, any non zero number (as string value, e.g. "42") will be converted as true value. String value "0" will be converted as false. String value "true" (case insensitive) will be converted as bool value true. String value "false" (case insensitive) will be converted as bool value false. Anything else will be converted as NULL value.

The syntax of a command to change data type is as follows:

ALTER TABLE table_name ALTER COLUMN column_name data_type;

Rename Table or Column

Since version: 1.4.1 You can rename a table using the following command:

ALTER TABLE table_name RENAME TO new_table_name;

Since version: 1.4.1 Or you can rename a specific column of a particular table.

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

Drop Column

Since version: 1.4.1 There is also an option to drop column from a specified table.

ALTER TABLE table_name DROP COLUMN column_name;

Add Constraint

Since version: 1.4.2 You can add constraint on one or multiple columns of a table. You can add one or multiple constraints. Keyword CONSTRAINT_TYPE has to be replaced with one of the constraint from this list. For example, if we want to add NOT NULL constraint on just one column, the command would look like this: "ALTER TABLE table_name ADD NOT NULL constraint_name (column_name);".

ALTER TABLE table_name ADD CONSTRAINT_TYPE constraint1_name
(column1_name [, column2_name, ...])
[, CONSTRAINT_TYPE constraint2_name (column1_name [, column2_name, ...])];

Drop Constraint

Since version: 1.4.2 You can drop constraint using constraint name. If the constraint name was not explicitly defined when creating constraint, the default name of the constraint was made up of the column name and constraint suffix, which depends on the constraint type (for more information see chapter create table). It will affect all columns that the constraint applied to. Note that dropping one constraints can be dependent (e.g. you cannot drop NOT NULL constraint when the UNIQUE constraint is still applied on that particular column).

ALTER TABLE table_name DROP CONSTRAINT constraint1_name
[, DROP CONSTRAINT constraint2_name, ...];

DROP TABLE

Since version: 1.4.1 Deletes chosen table. In order to run this command you need to select database first (see Console).

DROP TABLE table_name;