Creating a table
How to create CQL tables.
WITH
clause and
keyword arguments to configure table properties (caching, compaction, and so forth). See table_options. Create schema using cqlsh
Create table schema using cqlsh
. DataStax Enterprise does not support
dynamic schema generation because collisions can occur if multiple clients attempt to
generate tables simultaneously. To recover from collisions, follow the instructions in schema collision fix.
Primary Key
A Primary_key identifies the location and order of stored data. The primary key is defined when the table is created and cannot be altered. If you must change the primary key, create a new table schema and write the existing data to the new table. See ALTER TABLE for details on altering a table after creation.
The DataStax Enterprise database is a partition row store. The first element of the primary key, the partition key, specifies which node will hold a particular table row. At the minimum, the primary key must consist of a partition key. You can define a compound partition key to split a data set so that related data is stored on separate partitions. A compound primary key includes clustering columns that order the data on a partition.
- the size of the partitions.
- the order of the data within partitions.
- the distribution of the partitions among the nodes of the cluster.
Table name conventions
- To specify the keyspace that contains the table, put the keyspace name followed by a period before the table name: keyspace_name.table_name. This allows you to create a new table in a keyspace that is different from the one set for the current session (by the USE command, for example).
- To create a table in the current keyspace, just specify the new table name.
Column characteristics
CREATE TABLE cycling.cyclist_alt_stats ( id UUID PRIMARY KEY, lastname text, birthday timestamp, nationality text, weight text, height text );
CREATE TABLE cycling.whimsey ( id UUID PRIMARY KEY, lastname text, cyclist_teams set<text>, events list<text>, teams map<int,text> );
Collection
types cannot be nested. Collections can include frozen data types. For
examples and usage, see Freezing collection types.CREATE TABLE cycling.route (race_id int, race_name text, point_id int, lat_long tuple<text, tuple<float,float>>, PRIMARY KEY (race_id, point_id));
For
more information, see Tuple type.Create a user-defined type (UDTs) as a data type of several fields, using CREATE TYPE. It is best to create a UDT for use with
multiple table definitions. The user-defined column type (UDT) requires the
frozen keyword. The scope of a user-defined type
is the keyspace in which you define it. Use dot notation to access a type from a
keyspace outside its scope: keyspace name followed by a period followed the name
of the type, for example: test.myType
where
test
is the keyspace name and myType
is
the type name. The database accesses the type in the specified keyspace, but
does not change the current keyspace; otherwise, if you do not specify a
keyspace, the database accesses the type within the current keyspace. For examples and usage
information, see Using a user-defined type.
A counter is a special column used to store a number that is changed in increments. A counter can only be used in a dedicated table that includes a column of counter data type. For more examples and usage information, see Using a counter.