Create a table

To create a table, you must define the table name, column names, column data types, and a primary key. The cqlsh command-line tool is the primary tool for interacting with CQL to define schema.

Add the optional WITH clause and keyword arguments to configure table properties (caching, compaction, etc.) as described in the table options.

Dynamic schema generation and schema collision

It is important to note that dynamic schema generation is not supported. Schema collisions can occur if multiple clients attempt to generate tables simultaneously. Contact Support if you believe that a schema collision is occurring for resolution.

Prerequisites

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.

Simple table - single partition key in current keyspace

To create a table in the current keyspace, you can specify just the new table name:

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

In this example, first the cycling keyspace is selected, and then a table is created with three columns, the first of which is defined as a single PRIMARY KEY. That column, id, is a single partition key.

Simple table - single partition key in another keyspace

To create a table in another keyspace, you can specify both the keyspace and new table name:

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

The difference between the last example and this one is just that the keyspace name is used in the table creation.

Table with primary key separately specified

To define the PRIMARY KEY with one or more columns, add an additional item to the table creation:

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

The difference between the last example and this one is just that the PRIMARY KEY is specified separately.

Table with name of mixed case

To name a table with a mixed case, use double quotes:

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

Note the double quotes ("…​") around the table name, to ensure the table is created with the mixed case name.

Unless you need keyspaces or tables with uppercase or mixed case names, simplify by using lowercase only. If your keyspace or table creation commands use uppercase without double quotes, the name will be created with lowercase only.

Table - multi-column (composite) partition key

To define the PRIMARY KEY with a partition key of one or more columns:

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)
);

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.

Table - clustering columns

The last example showed one example of using a clustering column. Clustering columns in a primary key are all columns listed that are not part of the partition key. The partition key, as shown above, is either the first column defined in the primary key, or a composite partition key encased in parentheses.

Here is another example to make clear the parts of a primary key:

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);

In this example, The category column is the partition key, and the points column is a clustering column. It is important to remember that the primary key must uniquely identify a row within the cyclist_category table. 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 due to the addition of the table option WITH CLUSTER ORDER BY. Normally, columns are sorted in ascending alphabetical order.


Related information:

Creating a table with COMPACT STORAGE (for DSE 5.1 only)

Use WITH COMPACT STORAGE to create a table that is compatible with clients written to work with the legacy (Thrift) storage engine format.

CREATE TABLE sblocks (
  block_id uuid,
  subblock_id uuid,
  data blob,
  PRIMARY KEY (block_id, subblock_id)
)
WITH COMPACT STORAGE;

Using the WITH COMPACT STORAGE directive prevents you from defining more than one column that is not part of a compound primary key. A compact table with a primary key that is not compound can have multiple columns that are not part of the primary key.

A compact table that uses a compound primary key must define at least one clustering column. Columns cannot be added nor removed after creation of a compact table. Unless you specify WITH COMPACT STORAGE, CQL creates a table with non-compact storage.

Collections and static columns cannot be used with COMPACT STORAGE tables.

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com