Primary keys

Explore primary keys in YugabyteDB using YCQL

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

Before you start

The examples will run on any YugabyteDB universe.
To create a universe, see Set up YugabyteDB universe.

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