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 theclustering_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
-
Create a
users
table withuser_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 second entry with Rose
as the full_name
overrides the first entry because the user_id
is the same.
Table constraint
-
Create a
devices
table withsupplier_id
anddevice_id
as the partitioning columns andmodel
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 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)
-
Insert another entry with
supplier_id
,device_id
, andmodel_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.