Support for composite primary keys

thats hard… maybe @amaster507 @MichelDiz @BenW buddies have a clue how to solve that use case

This is one of the several standard features that SQL supports that DGraph doesn’t. For now, one work around is to do something like this:

type Node {
  id: String!
  version: String!
  composite: String! @id
}

Add the composite string as id__version so that it is forced to be unique. You could use a lambda to enforce this, although if you add it correctly, it will be be unique without a lambda.

J

2 Likes

It is not uuid, it is UID. That’s important, cuz they are two very different things.

I see. But how do you guarantee the uniqueness of the data?

You can simulate it using the lambda function as mentioned by Jonathan.

And I don’t think we gonna have this composite p.keys any time soon.

1 Like

The manual composite key makes sense from a logic standpoint but we lose the easy to understand get queries as instead of getNode(name: '..') {} it would be getNode(name_version: '${name}_${version}) {} which the user may not know the version. Thanks for replying!

The uniqueness of the data would be guaranteed by Dgraph would it not? Similar to how the @id field guarantees uniqueness on one field currently. And i see, ok. Would love to see this feature so I hope it can climb the roadmap at some point! thanks for the hard work!

1 Like

I think this is tracked/requested in Composite @id fields as well. App side concatenating data is possible (usually - you don’t always control the client) but annoying and doesn’t guarantee the component fields stay in sync w/ the id field after mutations/updates.

From what I see in your data example

[
   {
      "id": "1",
      "version": "1",
      "uniqueIdWithVersion": "1-1"
   },
   {
      "id": "1",
      "version": "2",
      "uniqueIdWithVersion": "1-2"
   }
]

Feels like the ID wouldn’t be unique. What part of the data would characterize it as unique?

Feels like both nodes are abstractly the same information but with different versions. So, it is the same “node” and the concatenation of the ID with the version makes it unique. Right?

In practice, they aren’t unique nodes but it represents an evolution of that data. My question was based on that premise. The uniqueness in the DB itself. This modeling can be done in DQL tho.

Cheers.

So this data example is a response to @jdgamble555 in regards to creating a unique field by concatenating the two fields I want to use as a pair to form uniqueness (a composite key). The field uniqueIdWithVersion would give uniqueness using Dgraph’s current capabilities but would lose some ease of use in regards to the GraphQL generated get* queries Dgraph generates (which is based on the @id fields). You are correct in that the id itself would not be unique. But the composite of {id, version} would be unique which is where this request originated from. Thanks for the further explanation!

You can’t use a get query on an @id type regardless, only an ID type. You would need to filter it.

query {
  queryNode(filter: { composite: { eq: $ID_$VERSION } }) {
    id
    version
    composite
  }
}

J

Ummm… this is incorrect, FYI.

https://dgraph.io/docs/graphql/schema/ids/

@amaster507 Good call. I was thinking this was one of those things they never added for some reason.

@Tyler_D - In that case, it is even easier:

query {
  getNode(id: 1, version: 2) {
    id
    version
    composite
  }
}

J

@jdgamble555 This is exactly what I am hoping to do! However, I need composite keys because the id and version fields are not unique on their own but only as a pair.

1 Like

Here is another reason why composite keys like this is close to impossible in Dgraph. This correlates to a very similar question

In Dgraph there you cannot have an index over two predicates because where would the index live if the predicates were not part of the same group. There is a control to manually move predicates to the same group, but if that is not manually done, then it is possible for the two predicates to be distributed across multiple groups (aka servers).

So logically how would you even implement this feature of a unique index across multiple predicates that may be distributed? This would be like saying, it would be like asking SQL to add a unique key constraint across two tables or even two different databases.

1 Like

Hmm I see, that is really too bad as having uniqueness be isolated to only a single field a significant drawback for using Dgraph. It is really needed in certain situations wherein you cant just “create” a composite key under one field because you do not control the querying (The queries are made by the end user).

1 Like

So, I disagree.

I think you’re confusing indexes and constraints in this situation.

What we want is to put @id on multiple fields at the same time. This is equivalent to a unique constraint on multiple fields (or primary key) in SQL. The $VARIABLE1__$VARIABLE2 is how noSQL handles this.

From my understanding, the @id directive indexes on the GraphQL level as middleware, not in the database itself. Because of that, it does not matter where and how the shards are stored in Dgraph, and ultimately in badger.

I do believe that in order for this GraphQl to be complete one day, @id on multiple fields should be on that feature list. However, it is not at the top of the list, because we just need to think more in Graph terms to remodel the data.

I do agree with you on this.

That being said, there are two work arounds currently:

type node {
  id: String! @id
  version: String! @id  
}

This could be translated to:

type node {
  id: String! @id
  version: [String!]
}

What we’re saying is that we want to require uniqueness for id and version together. Done.

The other work around of course is to block the ADD / UPDATE mutations, and use a Custom Lambda Mutation for both to enforce it.

EDIT: I would say the better workaround would be to allow @auth rules to use database values. That way you can have a noSQL sort of workaround where you enforce $fieldC = $fieldA + __ + $fieldB. Just a thought.

J

If i’m not wrong @Tyler_D you can already use @id in two or more fields.

As @jdgamble555 said i think it’s only middleware that ensures uniqueness (thats why @unique would make more sense here and this is another reason )

As it ensures uniqueness, It also grants the power to filter nodes using it, and I guess that’s the reason behind calling it @id. But I think it is confusing

But coming back to the main problem, we need uniqueness in the composition of those two unique values. And indeed, @auth rules that read current values in DB could at least impede the creation of a node if $fieldA + __ + $fieldB already exists in the DB. That would do a nice workaround to ensure uniqueness on creation.

we would still need pre-hooks in order to auto-calculate those composite-index as the combination, instead of forcing the graphql client to add them manually tho.

@loic Yes you can use more than 1 @id field but they all have to be individually unique. What I need is a composite primary key (where the 2 @id fields together define uniqueness).

yep yep i know, i have the same need for another use case @id as the combination of two things (composite index)

I still think this could be done solely on the GraphQl middlewhere layer, where we could perhaps have something like:

type Node @id(person, job) {
  id: ID!
  person: Person!
  job: Job!
  ...
}
query {
  getNode(person: '0x1', job: '0x2') {
    ...
  }
}

maybe internally it creates an _id field for the composite index, and would work the same as expected.

I am not sure if that would be less work then allowing an auth rule to do this.

You would need pre-hooks or the ability to create the @auth rule, but not necessarily both. With prehooks, you don’t need the @auth rule at all.

J

1 Like

I think the ideal case would be having both your @id middleware which seems an amazing idea to describe composite ids, and @unique to ensure uniqueness for certain fields and being able to filter by those, so something like this could be intuitive and powerful:

type Application @id(person, job) {
  id: ID!
  person: Person!
  job: Job!
  applicationCode: String! @Unique
  ...
}

And yes! I wish pre-hooks are a thing soon!

1 Like