Primary keys

The Primary Key constraint is a means to uniquely identify a specific row in a table via one or more columns. In YCQL, it should be defined either under the column_constraint or the table_constraint, but not under both:

• column_constraint: Columns can be either STATIC or declared as the PRIMARY KEY. Declaring a column as STATIC results in the same value being shared for all those rows that belong to the same partition (rows with the partition key). Declaring a column as a PRIMARY KEY makes that individual column its sole component.

• table_constraint: PRIMARY KEY defined as the table_constraint takes columns to form one or more partition keys and zero or more clustering keys. Syntactically, the order is to have the partition_key_column_list first, followed by the clustering_key_column_list.

Refer to the Grammar section for CREATE TABLE in YCQL. The PRIMARY KEY section includes details about the partition key, clustering key, and STATIC COLUMNS.

Examples

Column constraint

1. Create a users table with user_id as the primary key.

   CREATE TABLE users(user_id INT PRIMARY KEY, full_name TEXT);
   

2. Insert two rows into the table and check the entries.

   INSERT INTO users(user_id , full_name) VALUES (1, 'John');
   INSERT INTO users(user_id , full_name) VALUES (1, 'Rose');
   SELECT * FROM users;
   

    user_id | full_name
   ---------+-----------
          1 |       Rose
   

The second entry with Rose as the full_name overrides the first entry because the user_id is the same.

Table constraint

1. Create a devices table with supplier_id and device_id as the partitioning columns and model year as the clustering column.

   CREATE TABLE devices(supplier_id INT,
                       device_id INT,
                       model_year INT,
                       device_name TEXT,
                       PRIMARY KEY((supplier_id, device_id), model_year));
   

2. Insert three rows into the table and view the contents.

   INSERT INTO devices(supplier_id, device_id, device_name, model_year) VALUES (1, 101, 'iPhone', 2013);
   INSERT INTO devices(supplier_id, device_id, device_name, model_year) VALUES (1, 102, 'Pixel', 2011);
   INSERT INTO devices(supplier_id, device_id, device_name, model_year) VALUES (1, 102, 'Samsung S3', 2001);
   SELECT * FROM devices;
   

    supplier_id | device_id | model_year | device_name
   -------------+-----------+------------+-------------
              1 |       101 |       2013 |      iPhone
              1 |       102 |       2001 |     Samsung
              1 |       102 |       2011 |       Pixel
   
   (3 rows)
   

3. Insert another entry with supplier_id, device_id, and model_year as 1, 102, and 2011 respectively.

   INSERT INTO devices(supplier_id, device_id, device_name, model_year) VALUES (1, 102, 'MotoRazr', 2011);
   SELECT * FROM devices;
   

    supplier_id | device_id | model_year | device_name
   -------------+-----------+------------+-------------
              1 |       101 |       2013 |      iPhone
              1 |       102 |       2001 |     Samsung
              1 |       102 |       2011 |    MotoRazr
   

The row with device_name Pixel is replaced with MotoRazr.

Explore more examples

• Use column constraint to define a static column
• Example for clustering columns by ORDER
• Example to define the default expiration time for rows
• Create a table specifying the number of tablets