CREATE TABLE

Creates a new table in the specified (or current) keyspace. If the table already exists, no table is created and the statement returns an error. Use IF NOT EXISTS to suppress the error message.

Syntax

CREATE TABLE [ IF NOT EXISTS ] [<keyspace_name>.]<table_name>
( <column_definition> [ , ... ]
  [ , PRIMARY KEY ( ( <partition_key_column_name> [ , ... ] )
                  [ , ( <clustering_column_name> [ ... ] ) ] ) ] )
  [ [ WITH | AND ] <table_options> ]
  [ [ WITH | AND ] CLUSTERING ORDER BY ( <clustering_column_name> [ ASC | DESC ] [ , ... ] ) ] ;

Legend
Syntax conventions	Description
UPPERCASE	Literal keyword.
Lowercase	Not literal.
`< >`	Variable value. Replace with a user-defined value.
`[]`	Optional. Square brackets (`[]`) surround optional command arguments. Do not type the square brackets.
`( )`	Group. Parentheses ( `( )` ) identify a group to choose from. Do not type the parentheses.
`\|`	Or. A vertical bar (`\|`) separates alternative elements. Type any one of the elements. Do not type the vertical bar.
`...`	Repeatable. An ellipsis ( `...` ) indicates that you can repeat the syntax element as often as required.
`'<Literal string>'`	Single quotation (`'`) marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.
`{ <key> : <value> }`	Map collection. Braces (`{ }`) enclose map collections or key value pairs. A colon separates the key and the value.
`<<datatype1>,<datatype2>>`	Set, list, map, or tuple. Angle bracke ts ( `< >` ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.
`<cql_statement>;`	End CQL statement. A semicolon (`;`) terminates all CQL statements.
`[--]`	Separate the command line options from the command arguments with two hyphens ( `--` ). This syntax is useful when arguments might be mistaken for command line options.
`' <<schema\> ... </schema\>> '`	Search CQL only: Single quotation marks (`'`) surround an entire XML schema declaration.
`@<xml_entity>='<xml_entity_type>'`	Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.

Parameters

Parameter Description Default

keyspace_name

Optional. Name of the keyspace that contains the table. .Keyspace and table name conventions

The name of a keyspace or a table is a string of alphanumeric characters and underscores, but it must begin with a letter. If case must be maintained, the name must be encased in double quotes, such as "MyTable".

Since tables are defined within a keyspace, you can either use the keyspace as part of the table creation command, or create a table in the current keyspace. To specify the keyspace as part of a table name, use the keyspace name, a period (.), and table name, such as cycling.cyclist-stats.

If no name is specified, the current keyspace is used.

table_name

Name of the table.

column_definition

List of column_names and their CQL data types. The optional PRIMARY KEY clause specifies the primary key column if the table has only a single partition key. The optional STATIC keyword is used to define a static column, which has a single value shared by all rows in a partition.

column_name

Name of the column.

cql_type

CQL data type

column_list

List of columns that make up the primary key. The first column is the partition key. If there is more than one column in the partition key, enclose the columns in parentheses. Additional columns are clustering columns.

partition_key_column_name

Name of the partition key column. One or more partition key columns are required.

cluster_column_name

Name of the clustering column. Optional. One or more clustering columns can be defined. Also define ASC or DESC for the clustering order.

ASC

Options

Astra DB supports a single table option, which is specified using the WITH keyword.

Option Description Default

Option	Description	Default
`COMMENT`	Specify comments for the table.	`''`

COMMENT

Specify comments for the table.

''

Usage Notes

All tables must have a primary key, made up of a partition key and zero or more clustering columns. A partition key can be a single column or a composite partition key composed of multiple columns. A static column is shared by all rows in a partition and is stored only once per partition. A static column cannot be part of the primary key. Primary keys can include frozen collections and UDTs.

CREATE TABLE

Syntax

Parameters

Options

Usage Notes

Examples

Was this helpful?

Give Feedback