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.
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
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 (
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
id, is a single partition key.
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.
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.
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.
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_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.
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
More than one row with the same
category can exist, as long as that row contains a different
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.
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.