CREATE TABLE

This clause creates a new table.

See also ALTER TABLE and DROP TABLE.

Syntax

CREATE TABLE
  folder_name.table_name
[ ({ col_def }
        [,{ col_def }
        ]...
  ) 
]
[
  [AS subquery]
]

Parameters

folder_name: The name of your folder.

---

table_name: The name of the table.

Important

Each clause following the table name is optional for each table. However, at least either column names and data types or an AS subquery clause must be specified for each table.

---

col_def (optional): Specifies the properties for the columns. See further details under col_def details

---

AS subquery: This clause allows you to use a subquery when creating the table. A subquery is a subquery (see SELECT), whose returned rows are inserted into the table when it is created.

When the AS subquery clause is used and each column returned by the subquery has a column name (or, if it is an expression, a column alias is specified), the col_def clause can be omitted. The column names are taken from the subquery.

Restrictions for AS subquery clause

• In a CREATE TABLE statement that includes an AS subquery clause, a foreign key constraint definition is not allowed. However, you can create the table without the constraint and add it later with an ALTER TABLE statement.
• In a CREATE TABLE statement that includes an AS subquery clause, data types cannot be explicitly specified.

Table of Examples

• Example 1: Simple Table

col_def Details

This clause is used:

• as a subclause in the 'CREATE TABLE' statement
• as a subclause in add_column_clause in column_clause of 'ALTER TABLE' statement

col_name  [datatype]
          [DEFAULT [ON NULL] expr]
          ]

Parameters

col_name: The name of the column.

See also Rules for Naming Database Objects.

---

datatype: The data type of the column.

The data type must not be specified if the AS subquery clause is specified.

See also Data Types.

[NOT] NULL Constraint Details

Syntax

{constr_name} [NOT] NULL...

Parameters

constr_name: The name of the constraint.

---

NULL: This specification explicitly allows a column to contain NULL values. The default value is NULL.

---

NOT NULL: A NOT NULL specification prevents a column from containing NULL values.

To satisfy a NOT NULL constraint, each row in the table must contain a non-NULL value for the column.

Examples

Example 1: Simple Table

In this example, a simple table is created and filled with sample data (see also INSERT).

CREATE TABLE mkw_doku.my_table 
        (character_var STRING, 
          integer_var INTEGER, 
          decimal_var DECIMAL, 
          timestamp_var TIMESTAMP); 
INSERT INTO mkw_doku.my_table (character_var, integer_var, decimal_var, timestamp_var) 
                       VALUES ('First Row', 1, 0.1, '2024-12-01 16:33:14,134');

SELECT * FROM mkw_doku.my_table;

+---------------+-------------+-------------+-------------------------+
| CHARACTER_VAR | INTEGER_VAR | DECIMAL_VAR | TIMESTAMP_VAR           |
+---------------+-------------+-------------+-------------------------+
| First Row     | 1           | 0.1         | 2024-12-01 16:33:14,134 |
+---------------+-------------+-------------+-------------------------+