The Primary Key constraint is a means to uniquely identify a specific row in a table via one or more columns. To define a primary key, you create a constraint that is, functionally, a unique index applied to the table columns.
You might be surprised to learn that in the YSQL API, YugabyteDB allows you to update the values of a Primary Key column!
For example:
yugabyte=# CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR);
CREATE TABLE
yugabyte=# \d t1;
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
c1 | integer | | not null |
c2 | character varying | | |
Indexes:
"t1_pkey" PRIMARY KEY, lsm (c1 HASH)
yugabyte=# INSERT INTO t1 (SELECT 1, 'A') UNION ALL (SELECT 2, 'B');
INSERT 0 2
yugabyte=# SELECT * FROM t1;
c1 | c2
----+----
1 | A
2 | B
(2 rows)
byte=# UPDATE t1 SET c1 = c1 * 10;
UPDATE 2
yugabyte=# SELECT * FROM t1;
c1 | c2
----+----
10 | A
20 | B
(2 rows)
Keep in mind that you won’t be able to update a Primary Key column if that column is part of a RANGE or LIST partition.
For example:
yugabyte=# DROP TABLE t1;
DROP TABLE
yugabyte=# CREATE TABLE t1(c1 INT PRIMARY KEY, c2 VARCHAR) PARTITION BY LIST(c1);
CREATE TABLE
yugabyte=# CREATE TABLE t1_1 PARTITION OF t1 FOR VALUES IN (1);
CREATE TABLE
yugabyte=# CREATE TABLE t1_2 PARTITION OF t1 FOR VALUES IN (2);
CREATE TABLE
yugabyte=# INSERT INTO t1 (SELECT 1, 'A') UNION ALL (SELECT 2, 'B');
INSERT 0 2
yugabyte=# UPDATE t1 SET c1 = c1 * 10;
ERROR: no partition of relation "t1" found for row
DETAIL: Partition key of the failing row contains (c1) = (10).
To run the update you will first have to create additional partition tables to handle the new values.
For example:
yugabyte=# CREATE TABLE t1_10 PARTITION OF t1 FOR VALUES IN (10);
CREATE TABLE
yugabyte=# CREATE TABLE t1_20 PARTITION OF t1 FOR VALUES IN (20);
CREATE TABLE
yugabyte=# UPDATE t1 SET c1 = c1 * 10;
UPDATE 2
yugabyte=# SELECT * FROM t1;
c1 | c2
----+----
10 | A
20 | B
(2 rows)
Have Fun!