docs/_docs/sql-reference/ddl.adoc

// Licensed to the Apache Software Foundation (ASF) under one or more
// contributor license agreements.  See the NOTICE file distributed with
// this work for additional information regarding copyright ownership.
// The ASF licenses this file to You under the Apache License, Version 2.0
// (the "License"); you may not use this file except in compliance with
// the License.  You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
= Data Definition Language (DDL)

This section walks you through all data definition language (DDL) commands supported by Apache Ignite 3.0.

== CREATE TABLE

Creates a new table.


[.diagram-container]
Diagram(
NonTerminal('CREATE'),
NonTerminal('TABLE'),
Optional('IF NOT EXISTS'),
NonTerminal('table_name'),
NonTerminal('('),
Choice(1,
NonTerminal('constraint'),
NonTerminal('column_definition'),
NonTerminal(',')),
NonTerminal(')'),
End({type:'complex'})
)

[.diagram-container]
Diagram(
Start({type:'complex'}),
Optional(
Sequence(
NonTerminal('COLOCATE'),
Optional('BY'),
NonTerminal('column_list')
)
),
Optional(
Sequence(
NonTerminal('WITH'),
OneOrMore('param_name')
)
)
)


Parameters:

* `table_name` - name of the table. Can be schema-qualified.
* `table_column` - name and type of a column to be created in the new table.
* `IF NOT EXISTS` - create the table only if a table with the same name does not exist.
* `COLOCATED BY` - colocation key. The key can be composite. Primary key must include colocation key. Was `affinity_key` in Ignite 2.x.
* `WITH` - accepts the following additional parameters:

** `Replicas` - sets the number of partition copies, including the master copy.
** `Partitions` - sets the number of table partitions.
** `PRIMARY_ZONE` - sets the link:distribution-zones[Distriburion Zone].
** Other parameters, depending on the database engine.


Examples:

Creates a Person table:

[source,sql]
----
CREATE TABLE IF NOT EXISTS Person (
  id int primary key,
  city_id int,
  name varchar,
  age int,
  company varchar,
)
----
//
== ALTER TABLE
//
Modifies the structure of an existing table.
//
//[NOTE]
//====
//[discrete]
//=== Scope of ALTER TABLE
//Presently, Apache Ignite only supports addition and removal of columns.
//====
//
//* *ALTER TABLE IF EXISTS table LOGGING*
//
//[source,sql]
//----
//ALTER TABLE [IF EXISTS] tableName {LOGGING}
//----
//
//Parameters:
//
//- `IF EXISTS` - if applied to `TABLE`, do not throw an error if a table with the specified table name does not exist. If applied to `COLUMN`, do not throw an error if a column with the specified name does not exist.
//- `qualified_table_name` - the name of the table.
//- `LOGGING` - enables write-ahead logging for the table. The command is relevant only if Ignite link:https://ignite.apache.org/docs/3.0.0-alpha/persistence[persistence,window=_blank] is used.
//
//image::images/svg/alter_table_logging_bnf.svg[Embedded,opts=inline]
//
//* *ALTER TABLE IF EXISTS table NOLOGGING*
//
//[source,sql]
//----
//ALTER TABLE [IF EXISTS] tableName {NOLOGGING}
//----
//
//Parameters:
//
//- `IF EXISTS` - if applied to `TABLE`, do not throw an error if a table with the specified table name does not exist. If applied to `COLUMN`, do not throw an error if a column with the specified name does not exist.
//- `qualified_table_name` - the name of the table.
//- `NOLOGGING` - disables write-ahead logging for the table. The command is relevant only if Ignite link:https://ignite.apache.org/docs/3.0.0-alpha/persistence[persistence,window=_blank] is used.
//
//image::images/svg/alter_table_no_logging_bnf.svg[Embedded,opts=inline]
//

==== ALTER TABLE IF EXISTS table ADD COLUMN (column1 int, column2 int)

[.diagram-container]
Diagram(
  NonTerminal('ALTER TABLE'),
  Optional('IF EXISTS'),
  NonTerminal('qualified_table_name'),
  NonTerminal('ADD'),
  Optional('COLUMN'),
End({type:'complex'})
)

[.diagram-container]
Diagram(
  Start({type:'complex'}),
  NonTerminal('column_definition_or_list')
)


Parameters:

- `IF EXISTS` - if applied to `TABLE`, do not throw an error if a table with the specified table name does not exist. If applied to `COLUMN`, do not throw an error if a column with the specified name does not exist.
- `qualified_table_name` - the name of the table.
- `ADD` - adds a new column or several columns to a previously created table. Once a column is added, it can be accessed using link:sql-reference/dml[DML commands,window=_blank] and indexed with the CREATE INDEX statement.
- `COLUMN` - name of the defined column.
- `column_definition_or_list` - definition of the column to add to the table.



==== ALTER TABLE IF EXISTS table DROP COLUMN (column1, column2 int)

[.diagram-container]
Diagram(
NonTerminal('ALTER TABLE'),
Optional('IF EXISTS'),
NonTerminal('qualified_table_name'),
NonTerminal('DROP'),
Optional('COLUMN'),
End({type:'complex'})
)

[.diagram-container]
Diagram(
Start({type:'complex'}),
NonTerminal('column_list')
)


Parameters:

- `IF EXISTS` - if applied to `TABLE`, do not throw an error if a table with the specified table name does not exist. If applied to `COLUMN`, do not throw an error if a column with the specified name does not exist.
- `qualified_table_name` - the name of the table.
- `DROP` - removes an existing column or multiple columns from a table. Once a column is removed, it cannot be accessed within queries. Consider the following notes and limitations:
- `column_list` - the name of the list of columns to be removed.


- If the column was indexed, the index has to be dropped manually in advance by using the 'DROP INDEX' command.
- It is not possible to remove a column if it represents the whole value stored in the cluster. The limitation is relevant for primitive values.
Ignite stores data in the form of key-value pairs and all the new columns will belong to the value. It's not possible to change a set of columns of the key (`PRIMARY KEY`).

//Both DDL and DML commands targeting the same table are blocked for a short time until `ALTER TABLE` is in progress.

Schema changes applied by this command are persisted on disk. Thus, the changes can survive full cluster restarts.

Examples:

Add a column to the table:

[source,sql]
----
ALTER TABLE Person ADD COLUMN city varchar;
----

Add a column​ only if the table exists:

[source,sql]
----
ALTER TABLE IF EXISTS Missing ADD number bigint;
----

Add several columns to the table at once:

[source,sql]
----
ALTER TABLE Region ADD COLUMN (code varchar, gdp double);
----

Drop a column from the table:

[source,sql]
----
ALTER TABLE Person DROP COLUMN city;
----

Drop a column only if the table exists:

[source,sql]
----
ALTER TABLE IF EXISTS Person DROP COLUMN number;
----

Drop several columns from the table at once:

[source,sql]
----
ALTER TABLE Person DROP COLUMN (code, gdp);
----

//Disable write-ahead logging:
//
//[source,sql]
//----
//ALTER TABLE Person NOLOGGING
//----

== DROP TABLE

The `DROP TABLE` command drops an existing table.


[.diagram-container]
Diagram(
NonTerminal('DROP TABLE'),
Optional('IF EXISTS'),
NonTerminal('qualified_table_name')
)



Parameters:

- `IF EXISTS` - do not throw an error if a table with the same name does not exist.
- `qualified_table_name` - the name of the table. Can be schema-qualified.


//Both DDL and DML commands targeting the same table are blocked while the `DROP TABLE` is in progress.
//Once the table is dropped, all pending commands will fail with appropriate errors.

Schema changes applied by this command are persisted on disk. Thus, the changes can survive full cluster restarts.

Examples:

Drop Person table if the one exists:

[source,sql]
----
DROP TABLE IF EXISTS "Person";
----

== DESCRIBE TABLE

Returns information about the distribution zones of the table.

[.diagram-container]
Diagram(
NonTerminal('DESCRIBE TABLE'),
Optional('IF NOT EXISTS'),
NonTerminal('qualified_table_name'),
End({type:'complex'})
)

Parameters:

- `IF EXISTS` - do not throw an error if a table with the same name does not exist.
- `qualified_table_name` - the name of the table. Can be schema-qualified.

== CREATE INDEX

Creates a new index.

NOTE: The index cannot include the same column more than once.

[.diagram-container]
Diagram(
  NonTerminal('CREATE INDEX'),
  Optional('IF NOT EXISTS'),
  NonTerminal('name'),
  NonTerminal('ON'),
  NonTerminal('qualified_table_name'),
  End({type:'complex'})
)

[.diagram-container]
Diagram(
  Start({type:'complex'}),
  Optional(
   Sequence(
    NonTerminal('USING'),
    Choice (0,'HASH','TREE')
   )
  ),
  Optional('column_definition')
)


Parameters:


* `name` - name of the index.
* `qualified_table_name` - name of the table to create the index on. Can be schema-qualified.
* `IF NOT EXISTS` - create the index only if an index with the same name does not exist.
* `USING` - specifies whether the command creates a sorted index or a hash index. Possible values: `HASH`, `TREE`. Default is `TREE`.


//NOTE: Add image

Examples:

Create index Persons for Person table:

[source,sql]
----
CREATE INDEX IF NOT EXISTS Persons ON Person (column1)
----

== DROP INDEX

[.diagram-container]
Diagram(
NonTerminal('DROP INDEX'),
Optional('IF EXISTS'),
NonTerminal('index_name')
)

Parameters:

- `index_name` - the name of the index.
- `IF EXISTS` - do not throw an error if an index with the specified name does not exist.



Examples:

Drop index if the one exists:

[source,sql]
----
DROP INDEX IF EXISTS Persons;
----

== Grammar Reference

=== column_definition_or_list

[.diagram-container]
Diagram(
Group(Sequence(
Optional('('),
Choice(0,Sequence(
NonTerminal('column_name'),
NonTerminal('data_type')),
NonTerminal(',')
),
Optional(')')
), 'column_definition_or_list')
)


Referenced by:

* link:sql-reference/ddl#alter-table[ALTER TABLE]

'''

=== column_list


[.diagram-container]
Diagram(Group(Sequence(
Optional('('),
OneOrMore('column_name', ','),
Optional(')')))
)


Referenced by:

* link:sql-reference/ddl#alter-table[ALTER TABLE]

'''

=== constraint

[.diagram-container]
Diagram(Group(Sequence(
Optional(Sequence(NonTerminal('Constraint'),NonTerminal('constraint_name')
)),
NonTerminal('PRIMARY KEY'),
NonTerminal('('),
OneOrMore('column_name', ','),
NonTerminal(')')
),'constraint')
)


Referenced by:

* link:sql-reference/ddl#create-table[CREATE TABLE]

'''

=== qualified_table_name

[.diagram-container]
Diagram(Group(Sequence(
Optional(Sequence(NonTerminal('schema'),NonTerminal('.')
),),
NonTerminal('table_name')
),'qualified_table_name')
)

Referenced by:

* link:sql-reference/ddl#сreate-table[CREATE TABLE]
* link:sql-reference/ddl#alter-table[ALTER TABLE]
* link:sql-reference/ddl#drop-table[DROP TABLE]

'''

=== column_definition
//NOTE: Replace code with image

[.diagram-container]
Diagram(
Group(Sequence(
NonTerminal('column_name'),
NonTerminal('DATA TYPE'),
Optional(Sequence(Optional('NOT'),NonTerminal('NULL')))
), 'column_definition'),
End({type:'complex'})
)

[.diagram-container]
Diagram(
Start({type:'complex'}),
Group(Sequence(
Optional(Sequence(NonTerminal('DEFAULT'), NonTerminal('literal_value'))),
NonTerminal('PRIMARY KEY')
), 'column_definition')
)


Parameters:

* `ASC` or `DESC` - specifies that the column should be sorted in an ascending or descending order respectively.
* `NULLS` - specifies whether null values will be at the start or at the end of the index. Possible values: `FIRST`, `LAST`.

Referenced by:

* link:sql-reference/ddl#сreate-index[CREATE INDEX]

'''

=== tableColumn
//NOTE: Replace code with image


[.diagram-container]
Diagram(Group(Sequence(
NonTerminal('columnName'),
NonTerminal('columnType'),
Optional(Sequence(Optional('NOT'),NonTerminal('NULL')
),),
Optional('DEFAULT')
),'tableColumn')
)

Parameters:

* `[NOT] NULL` - specifies that values in the column always contain null or not null values.
* `DEFAULT` - specifies a default value for the column. You can specify a constant value, or use a link:sql-reference/ddl#system-functions[system function] to generate a value.

'''

== System Functions

=== gen_random_uuid

This function generates a random UUID value each time it is called.

Example:

[source,sql]
----
CREATE TABLE t (id varchar default gen_random_uuid primary key, val int)
----

Referenced by:

* link:sql-reference/ddl#create-table[CREATE TABLE]