6

Can I create compound primary key in RethinkDB ? For example if I have table with next documents strucure {authorsId: '371df80c-2efd-48f9-ac37-3edcdb152fb1', postsId: '905e0c92-adcb-4095-98df-fa08ca71b900'} how can I create primary key on both together authorsId and postsId. Or if I cannot do this how should I use many to many relationship in three tables way. Have I use one field as primary key and second as secondary index ?

abilash
  • 897
  • 3
  • 12
  • 32

2 Answers2

3

Primary keys can only be on one column but you can put postsId and authorsId in an array and use that as primary key value.

See here for more information: https://www.rethinkdb.com/api/javascript/table_create/

EDIT

The primary key is still just one field but you can use [postsId, authorsId] as the value for that column and achieve the same. (added back from comment to answer).

Spiky
  • 509
  • 4
  • 11
Oliver
  • 11,857
  • 2
  • 36
  • 42
  • When I put it into array there is exception: `Expected type STRING but found ARRAY` – abilash Nov 07 '15 at 18:46
  • here is query `r.db('Rbac').tableCreate('Test', {primaryKey: ['id1', 'id2']})` – abilash Nov 07 '15 at 18:47
  • This is not right. You cannot create primary key on multiple fields. – kureikain Nov 07 '15 at 18:48
  • 1
    Ah, let me rephrase me answer, it's confusing. The primary key is still just one field but you can use `[postsId, authorsId]` as the value for that column and achieve the same. – Oliver Nov 07 '15 at 19:31
  • What 21Zoo recommend is concat string of postId and authorId to generate a single string then use it as primary key. In other word, you can set the `id` field yourself, you don't have to let RethinkDB automatically generate your id. – kureikain Nov 07 '15 at 19:54
  • Not concat them as strings but put them into an array but the result is pretty much the same. – Oliver Nov 07 '15 at 20:34
  • 1
    @abilash: `r.db('Rbac').tableCreate('Test', {primaryKey: ['id1', 'id2']})` gives me a `Expected type STRING but found ARRAY in` in RethinkDB 2.3.4 – Stefan Walther Jul 22 '16 at 21:56
  • 2
    i think what the answer is saying is this. `r.db('test').tableCreate('mytable', {primaryKey: 'myprimary'})` and then when you do a read, insert, or update `r.db('test').table('mytable').insert({myprimary: ['id1value', 'id2value'], othervalue: 'something'})` and `r.db('test').table('mytable').update({myprimary: ['id1value', 'id2value'], othervalue: 'newthing'})` and `r.db('test').table('mytable').get(['id1value', 'id2value'])`. tested on data explorer – vbranden Sep 11 '16 at 12:23
1

RethinkDB primary key is for a single column, you can change it to any field instead of the default id. When you call tableCreate, you can pass a string for primaryKey to use that column as primary key.

You cannot create primary key on multiple columns. Though, you can create secondary index on multi columns. However, RethinkDB doesn't have the unique index effect https://github.com/rethinkdb/rethinkdb/issues/1716.

If you want to do many to many relation ships, use 3 tables, you can do this:

Table T1: id, fields

Table T2: id, fields2

Pivot table P: t1_id, t2_id

Then you can use eqJoin to join data, or concatMap/getAll. Example:

r.table("P").eq_join("t1_id", r.table("T1"))
  .zip()
  .eq_join("t2_id", r.table("T2"))
  .zip()

Basically from the pivot table, you join data with first table, then continue to join with second table.

You can read more about data table join https://www.rethinkdb.com/docs/table-joins/#many-to-many-relations in here.

kureikain
  • 2,304
  • 2
  • 14
  • 9
  • Thanks for feedback, I have one more answer - Doesn't primaryKey unique too in RethinkDB ? – abilash Nov 07 '15 at 18:58
  • Ups sorry I mean 'question' instead 'answer' in prev comment – abilash Nov 07 '15 at 19:08
  • And I am confused with next suggestion - when we are creating pivot table we need to set primary key (t1_id or t2_id) but primary key a unique and there were problems with store values in pivot table – abilash Nov 07 '15 at 19:13
  • Does that mean that we need 3rd column ? – abilash Nov 07 '15 at 19:15
  • Primary key is unique. On pivot table, it doesn't have any constraint to guarantee unique ness of [t1_id, t2_id]. Those limitation comes from the distribution of database, as in issue 1716 https://github.com/rethinkdb/rethinkdb/issues/1716. So, it's up to your application to deal with this. And example: when you insert record to pivot table, you can use extra primary `id` field with value = "${t1_id}${t2_id" to prevent duplication. So yeah, we have to deal with this unique inside our application. – kureikain Nov 07 '15 at 19:47
  • It's a little bit weird. Why here https://www.rethinkdb.com/docs/table-joins/ we have described author_post table with 2 column if actually we can not add 2 equal values in primary key column – abilash Nov 07 '15 at 20:01
  • It's just an example, and author of that article probably didn't think of uniqueness in pivot table, including myself. I'm happy with having duplication on pivot table. But your use case is more strict, and you need uniqueness on pivot table. RethinkDb does come with its own limitation. We need to be a bit flexible to deal with it. I'm giving an example on a way to reduce that, but it may not perfect, as you see. – kureikain Nov 07 '15 at 20:27
  • Can you change your primary key once you've created a table? – backdesk Apr 22 '16 at 14:16