CREATE TABLE

CREATE TABLE syntax

The invocation of CREATE TABLE creates a table with the specified data schema and primary key columns (PRIMARY KEY). It also allows defining secondary indexes on the created table.

CREATE [TEMP | TEMPORARY] TABLE table_name (
    column1 type1,
    column2 type2 NOT NULL,
    ...
    columnN typeN,
    INDEX `<index_name>`
      [GLOBAL|LOCAL]
      [UNIQUE]
      [SYNC|ASYNC]
      [USING <index_type>]
      ON ( <index_columns> )
      [COVER ( <cover_columns> )]
      [WITH ( <parameter_name> = <parameter_value>[, ...])]
    ...
    PRIMARY KEY ( column, ... ),
    FAMILY column_family ( family_options, ... )
)
WITH ( key = value, ... )

YDB supports two types of tables:

The table type is specified by the STORE parameter in the WITH clause, where ROW indicates a row-oriented table and COLUMN indicates a column-oriented table:

CREATE <table_name> (
  columns
  ...
)
WITH (
  STORE = COLUMN -- Default value ROW
)

By default, if the STORE parameter is not specified, a row-oriented table is created.

Database object naming rules

Every scheme object in YDB has a name. In YQL statements, object names are specified by identifiers that can be enclosed in backticks or not. For more information on identifiers, refer to Keywords and identifiers.

Scheme object names in YDB must meet the following requirements:

  • Object names can include the following characters:
    • uppercase latin characters
    • lowercase latin characters
    • digits
    • special characters: ., -, and _.
  • Object name length must not exceed 255 characters.
  • Objects cannot be created in folders, which names start with a dot, such as .sys, .medatata, .sys_health.

Column naming rules

Column names in YDB must meet the following requirements:

  • Column names can include the following characters:
    • uppercase latin characters
    • lowercase latin characters
    • digits
    • special characters: - and _.
  • Column names must not start with the system prefix __ydb_.

Examples of table creation

CREATE TABLE <table_name> (
  a Uint64,
  b Uint64,
  c Float,
  PRIMARY KEY (a, b)
);

For both key and non-key columns, only primitive data types are allowed.

Without additional modifiers, a column acquires an optional type and allows NULL values. To designate a non-optional type, use the NOT NULL constraint.

Specifying a PRIMARY KEY with a non-empty list of columns is mandatory. These columns become part of the key in the order they are listed.

Example of creating a row-oriented table using partitioning options:

CREATE TABLE <table_name> (
  a Uint64,
  b Uint64,
  c Float,
  PRIMARY KEY (a, b)
)
WITH (
  AUTO_PARTITIONING_BY_SIZE = ENABLED,
  AUTO_PARTITIONING_PARTITION_SIZE_MB = 512
);

Such code will create a row-oriented table with automatic partitioning by partition size (AUTO_PARTITIONING_BY_SIZE) enabled, and with the preferred size of each partition (AUTO_PARTITIONING_PARTITION_SIZE_MB) set to 512 megabytes. The full list of row-oriented table partitioning options can be found in the Partitioning row-oriented tables section.

CREATE TABLE table_name (
  a Uint64 NOT NULL,
  b Timestamp NOT NULL,
  c Float,
  PRIMARY KEY (a, b)
)
PARTITION BY HASH(b)
WITH (
  STORE = COLUMN
);

Example of creating a column-oriented table with an option to specify the minimum physical number of partitions for storing data:

CREATE TABLE table_name (
  a Uint64 NOT NULL,
  b Timestamp NOT NULL,
  c Float,
  PRIMARY KEY (a, b)
)
PARTITION BY HASH(b)
WITH (
  STORE = COLUMN,
  AUTO_PARTITIONING_MIN_PARTITIONS_COUNT = 10
);

This code will create a columnar table with 10 partitions. The full list of column-oriented table partitioning options can be found in the {#T} section.

When creating row-oriented tables, it is possible to specify:

When creating column-oriented tables, it is possible to specify:

Previous
CREATE OBJECT TYPE SECRET
Next
SECONDARY INDEX