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 ] [ , ... ] ) ] ;
| Syntax conventions | Description |
|---|---|
UPPERCASE |
Literal keyword. |
Lowercase |
Not literal. |
|
Variable value. Replace with a user-defined value. |
|
Optional.
Square brackets ( |
|
Group.
Parentheses ( |
|
Or.
A vertical bar ( |
|
Repeatable.
An ellipsis ( |
|
Single quotation ( |
|
Map collection.
Braces ( |
|
Set, list, map, or tuple.
Angle brackets ( |
|
End CQL statement.
A semicolon ( |
|
Separate the command line options from the command arguments with two hyphens ( |
|
Search CQL only: Single quotation marks ( |
|
Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files. |
Parameters
| Parameter | Description | Default |
|---|---|---|
|
Optional. Name of the keyspace that contains the table.
Separated from the table name with a period, such as Enclose in double quotes if the keyspace name contains uppercase letters. |
If no name is specified, the current keyspace is used. |
|
Name of the table. Enclose in double quotes if the table name contains uppercase letters. |
|
|
List of column names and their CQL data types.
The optional |
|
|
List of columns that make up the primary key.
The first column is the partition key ( |
|
|
Name of the partition key column. One or more partition key columns are required. |
|
|
Name of the clustering column.
Optional. One or more clustering columns can be defined.
Also define |
|
|
Options for the table, specified using the |
Varies by option |
|
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 |
Not set. If a table with the same name already exists in the same keyspace, an error occurs and the operation fails. |
Options
Specify table options using the WITH keyword.
Each option can have a single value.
Some options have subproperties that are specified using a simple JSON format, with key-value pairs in a comma-separated list enclosed by curly braces.
Most table options are managed by DataStax as part of the Astra DB service offering.
If any managed options are set in the CREATE TABLE statement, they are ignored and throw a warning.
| Option | Description | Default |
|---|---|---|
|
Specify comments for the table. |
|
|
TTL (Time To Live) in seconds, where zero is disabled.
The maximum configurable value is If the value is greater than For more information, see Data with time-to-live (TTL). |
|
|
Specifies the table extensions. |
|
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
idcolumn is specified as a single partition key in thePRIMARY KEYclause.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 KEYkeyword 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 KEYclause, enclosed in parentheses. Nested parenthesis are required when there are clustering columns.In this example,
race_yearandrace_nameare the two columns that comprise the composite partition key. They are encased in parentheses, inside thePRIMARY KEYdefinition. The columnrankis 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
categorycolumn is the partition key, and thepointscolumn 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
categorycan exist, as long as that row contains a differentpointsvalue.All the rows with the same
categorywill sort by thepointsvalues in descending order based on theCLUSTER ORDER BYoption. 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 ... ;
Additional CREATE TABLE examples
- Create a table in the current keyspace
-
When creating a table in the current keyspace (selected with the
USEcommand), 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.