databasedistributed-databaseyugabytedb

Is it possible to model one-to-many mappings in Yugabyte YCQL?


For example,

key          | value
-------------+-------
primary_key1 | v1
primary_key1 | v2

Is the above allowed where the same key has multiple values? Or does one need to have an explicit clustering key? If I have a table with 2 columns, can the value be the clustering key?


Solution

  • Primary key has to be unique. It's how you distinguish rows from one another. You can use for value a collection column (map,set,list):

    cqlsh> create table david.test(key TEXT, value SET<TEXT>, PRIMARY KEY(key));
    cqlsh> insert into david.test(key,value) VALUES ('primary_key1', {'v1','v2'});
    cqlsh> select * from david.test;
    
     key          | value
    --------------+--------------
     primary_key1 | {'v1', 'v2'}
    

    The other way is to add the clustering key. key will be the partition column, while value will be the clustering key (which will change the primary key to be (key,value)):

    cqlsh> create table david.test(key TEXT, value TEXT, PRIMARY KEY((key), value)) WITH CLUSTERING ORDER BY (value DESC);
    cqlsh> insert into david.test(key,value) values('primary_key1', 'v1');
    cqlsh> insert into david.test(key,value) values('primary_key1', 'v2');
    cqlsh> select * from david.test;
    
     key          | value
    --------------+-------
     primary_key1 |    v2
     primary_key1 |    v1
    

    Here, key is the partition value. The primary key is (key,value);