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.
Prerequisites
-
Understand the use and importance of the primary key in table schema.
-
Determine the data types that each column will require.
-
Start
cqlsh
.
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 ( |
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.