Home > Explore > Indexes and Constraints >

Primary Key

YSQL
YCQL

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

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.

The PRIMARY KEY section under the YCQL API includes details about the partition key, clustering key, and STATIC COLUMNS.

Examples

To run the examples below, follow these steps to create a local cluster or in Yugabyte Cloud.
Use the YCQL shell for local clusters, or Connect using Cloud shell for Yugabyte Cloud, to create a keyspace and a table.

Column constraint

Create a users table with user_id as the primary key.

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

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 entry of the second row with Rose as the full_name overrides the first one since the user_id is the same.

Table constraint

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));

Insert three rows to 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)

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;

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 gets overridden with MotoRazr.

Primary Key

Examples

Column constraint

Table constraint

Explore more examples