CREATE TABLE

Creates a table in the specified or current keyspace.

To create a table, you must define the table name, column names, column data types, and a primary key. Optionally, you can set table properties, like compaction and compression. Omitted options use default values.

For more information about table architecture and designing performant tables, see Data modeling for Apache Cassandra-based databases and CQL data types.

Dynamic schema generation and schema collision

Astra doesn’t support dynamic schema generation. Schema collisions can occur if multiple clients attempt to generate tables simultaneously.

Contact IBM Support for assistance with possible schema collisions.

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 brackets ( `< >` ) 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

Parameter	Description	Default
`keyspace_name`	Optional. Name of the keyspace that contains the table. Separated from the table name with a period, such as `cycling_keyspace.cycling_table`. Enclose in double quotes if the keyspace name contains uppercase letters.	If no name is specified, the current keyspace is used.
`table_name`	Name of the table. Enclose in double quotes if the table name contains uppercase letters.
`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`
`IF NOT EXISTS`	Allows silent skip by checking for existing tables with the same name in the same keyspace. If a match is found, the operation silently does nothing (no error is thrown). If no match is found, the operation creates the table. This option is set immediately after `CREATE TABLE`. It isn’t part of the `WITH` clause.	Not set. If a table with the same name already exists in the same keyspace, an error occurs and the operation fails.

keyspace_name

Optional. Name of the keyspace that contains the table. Separated from the table name with a period, such as cycling_keyspace.cycling_table.

Enclose in double quotes if the keyspace name contains uppercase letters.

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

table_name

Name of the table. Enclose in double quotes if the table name contains uppercase letters.

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

IF NOT EXISTS

Allows silent skip by checking for existing tables with the same name in the same keyspace. If a match is found, the operation silently does nothing (no error is thrown). If no match is found, the operation creates the table.

This option is set immediately after CREATE TABLE. It isn’t part of the WITH clause.

Not set. If a table with the same name already exists in the same keyspace, an error occurs and the operation fails.

Options

Table options are managed by DataStax as part of the Astra DB service offering. The only user-defined table option is WITH COMMENT, which is a string description of the table with no functional use. If any other options are set, they are ignored or cause the command to fail.

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.

Primary key examples

Table with one partition key (single-column primary key)

In this example, the table is created with three columns. The id column is specified as a single partition key in the PRIMARY KEY clause.

CREATE TABLE cycling.cyclist_name (
  id UUID,
  lastname text,
  firstname text,
  PRIMARY KEY (id)
);

For simple primary key definitions, you can also append the PRIMARY KEY keyword to the column definiton:

CREATE TABLE cycling.cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);

Multi-column (composite) partition key

To define a composite partition key, specify all relevant columns in the PRIMARY KEY clause, enclosed in parentheses. Nested parenthesis are required when there are clustering columns.

In this example, race_year and race_name are the two columns that comprise the composite partition key. They are encased in parentheses, inside the PRIMARY KEY definition. The column rank is an additional column that is a clustering column, as the next section describes.

CREATE TABLE IF NOT EXISTS cycling.rank_by_year_and_name (
  race_year int,
  race_name text,
  cyclist_name text,
  rank int,
  PRIMARY KEY ((race_year, race_name), rank)
);

Clustering columns

Within the primary key definition, the clustering columns are the columns that aren’t part of the partition key. As shown in the previous example, the partition key is either the first column defined in the primary key, or a composite partition key wrapped in parentheses.

In the following example, the category column is the partition key, and the points column is a clustering column:

CREATE TABLE IF NOT EXISTS cycling.cyclist_category (
  category text,
  points int,
  id UUID,
  lastname text,
  PRIMARY KEY (category, points)
)
WITH CLUSTERING ORDER BY (points DESC);

Remember that the primary key must uniquely identify a row within the table. In this example, more than one row with the same category can exist, as long as that row contains a different points value.

All the rows with the same category will sort by the points values in descending order based on the CLUSTER ORDER BY option. By default, columns are sorted in ascending alphabetical order.

Column definition examples

The following examples demonstrate how to define columns with different data types in a table schema.

Create a table with a frozen UDT column

Create the race_winners table that has a frozen user-defined type (UDT):

CREATE TABLE IF NOT EXISTS cycling.race_winners (
  cyclist_name FROZEN<fullname>,
  race_name text,
  race_position int,
  PRIMARY KEY (race_name, race_position)
);

See CREATE TYPE for information on creating UDTs. UDTs can be created unfrozen if only non-collection fields are used in the user-defined type creation. If the table is created with an unfrozen UDT, then individual field values can be updated and deleted.

Create a table with a geospatial type column

CREATE TABLE cycling.geospatial (
  id text PRIMARY KEY,
  point 'PointType',
  linestring 'LineStringType'
);

The resulting table definition shows the class names for the geospatial types used in the table columns. In the following example, the WITH clause is truncated to focus on the column definitions.

CREATE TABLE cycling.geospatial (
    id text PRIMARY KEY,
    linestring 'org.apache.cassandra.db.marshal.LineStringType',
    point 'org.apache.cassandra.db.marshal.PointType'
) WITH ... ;

Table options examples

Additional CREATE TABLE examples

Create a table in the current keyspace

When creating a table in the current keyspace (selected with the USE command), you can omit the keyspace name:

CREATE TABLE cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);

Create a table with name of mixed case

If the table name has both uppercase and lowercase letters, use double quotes to preserve the casing:

CREATE TABLE cycling."addThis" (
  a int PRIMARY KEY
);

Unless you need keyspaces or tables with uppercase or mixed case names, DataStax recommends using lowercase only. If your keyspace or table creation commands use uppercase without double quotes, the name is normalized to lowercase.

CREATE TABLE

Dynamic schema generation and schema collision

Syntax

Parameters

Options

Usage Notes

Primary key examples

Column definition examples

Create a table with a frozen UDT column

Create a table with a geospatial type column

Table options examples

Additional CREATE TABLE examples

Was this helpful?

Give Feedback