Feature Request - Multiple and Single Primary and Unique Keys

[jdgamble555]

Related to this issue for reference.

Keys

Composite Key - has more than one attribute
Compound Key - a specific composite key where both attributes are also foreign keys

PK (Primary Key)

• Right now, we can do this:

Single PK or @id (external id)

type User {
  username: String! @id
  name: String!
}

SQL

ADD PRIMARY KEY(username)

---

Composite PK or @id

type User {
  username: String! @id
  email: String! @id
}

SQL

ADD PRIMARY KEY(username, email)

---

Unique Keys (Unique)

• What we need to be able to do:

Single Unique or @unique

type User {
  id: ID! @id
  username: String! @unique
  name: String!
}

SQL

ADD CONSTRAINT UNIQUE (username)

---

Multiple Uniques or @unique

type User {
  id: ID! @id
  username: String! @unique
  email: String! @unique
}

SQL

ADD CONSTRAINT UNIQUE (username)
ADD CONSTRAINT UNIQUE (email)

---

Composite Uniques or @unique

type User @unique(fields: ["firstName", "lastName"]) {
  email: String!
  firstName: String!
  lastName: String!
  ...
}

SQL

ADD CONSTRAINT UNIQUE (firstName, lastName)

Compound Keys - composite keys on foreign keys or nested fields

type Book {
  id: ID!
  name: String!
  ...
}
type User {
  id: ID!
  name: String!
  reviews: [Review]!
  ...
}
type Review @unique(fields: ["reviewer.id", "book.id"]) {
  reviewer: User!
  book: Book!
  createdAt: DateTime!
}

SQL

In SQL, there would be a composite pk on the junction table [book_id_fk, user_id_fk, created_at]:

ADD PRIMARY KEY(book_id_fk, user_id_fk)

So theoretically, there should be a unique key on reviewer.id and book.id. This should also be possible for one foreign key, although I can't think of an example.

---

UPDATE: 11/2/22 - The @unique (not the @id) should be able to be null.

type User {
  id: ID! @id
  username: String @unique  // no ! in input
  email: String! @unique
}

Referring to this post, you should also be able to get multiple fields based on their COMPOSITE primary or unique key(s) as well as individual unique keys, which is how it currently works:

query {
  getUser(username: 'jdgamble555', email: 'someone@something.com') {
    username
    ...
  }
}

So we really have a few feature requests here:

1. Possibly rename @id to @unique
2. Allow top level @unique directive for multiple "composite" unique constraints
3. Allow "composite" unique constraints on nested fields (only one level), same as junction table
4. Make sure you can search with "get" for the composite fields
5. Allow nullable unique, as "no value" should not break the constraint

#2 is the main request here

---

• The GraphQL schema should fail if someone tries to add this directive on a field where there are "non unique" values.
• Some of this should be build on the DQL level up, which is a different feature request. This should be implemented first.

J

---

Just for more reference - outcaste-io/issues#26

[rderbier]

Splitting in several requests for tracking

• created DQL level @unique - [ENHANCEMENT]: Add @unique constraints to predicates in DQL schema #8865
• created GraphQL level @unique - [ENHANCEMENT]: add @unique constraint in GraphQL Schema #8866
• Composite unique constraint in GraphQL - [ENHANCEMENT]: Add Composite unique constraint in GraphQL schema #8867