Composite key support

[noedelcroix]

Hello,
A really missing functionality support is the composite management. Directus doesn't permit to create a unique composite key from GUI and even we create the composite key from SQL command (with ALTER TABLE <table name> ADD UNIQUE <key name>(<indexes>);), no errors are thrown...

[NilsBaumgartner1994]

I would like to see unique composite keys.

For example: A user can set allergic markings, which should not be dublicate in the junction table

[u12206050]

The easiest and simplest way to do this is adding a constraint on the two columns that should be unique.
The reason a single will always be preferred over two is both for navigation (CRUD) purposes as well as keeping things simple.

If you want here is a simple migration for creating a composite index on two columns (OPTIONALLY MORE).

// extensions/migrations/20220625-somethings-composite-index.js

module.exports = {
    async up(knex) {
        await knex.schema.table('somethings', (table) => {
            table.unique(['thing', 'other']);
        });
    },

    async down(knex) {
        await knex.schema.table('somethings', (table) => {
            table.dropUnique(['thing', 'other']);
        });
    },
};

[NilsBaumgartner1994]

How would I create the constraint in the GUI? Beste Grüße, Nils Baumgartner

…

Am 28.06.2022 um 19:49 schrieb Gerard Lamusse ***@***.***>:  The easiest and simplest way to do this is adding a constraint on the two columns that should be unique. The reason a single will always be preferred over two is both for navigation (CRUD) purposes as well as keeping things simple. If you want here is a simple migration for creating a composite index on two columns (OPTIONALLY MORE). // extensions/migrations/20220625-somethings-composite-index.js module.exports = { async up(knex) { await knex.schema.table('somethings', (table) => { table.unique(['thing', 'other']); }); }, async down(knex) { await knex.schema.table('somethings', (table) => { table.dropUnique(['thing', 'other']); }); }, }; — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.

[rijkvanzanten]

There's currently no support for composite unique/foreign/primary keys 👍🏻

[ozum]

@rijkvanzanten, as I advocated at #17185, this should be considered a bug, because composite keys are part of SQL standard and this is a blocking issue for databases with composite primary keys.

[cube-dan]

I needed a composite unique key across two M2M fields. To get around this missing feature I ended up writing a filter() hook implementation. Here's some example code:

src/index.js within the generated extension

// a custom re-implementation of the Directus RecordNotUniqueException; see below.
import { RecordNotUniqueException } from './RecordNotUniqueException';

export default ({ filter }, { exceptions, services }) => {

  const { ItemsService } = services;

  filter('assignment.items.create', async (payload, collection, ctx) => {
    
    const { accountability, schema } = ctx;
    const Assignment = new ItemsService('assignment', { schema: schema, accountability: accountability });
    
    // get the two M2M field values from the incoming payload
    const {item_id, profile_id} = payload;

    // do we already have an existing [profile]>-<[item] combination?
    const assignment = await Assignment.readByQuery({
      filter: {
        item_id: {
          _eq: item_id,
        },
        profile_id: {
          _eq: profile_id,
        },
      },
    });

    // stop the creation of the record if an existing [profile_id]>-<[item_id] record already exists
    if(typeof assignment[0] !== 'undefined'){
      throw new RecordNotUniqueException('[item]>-<[profile]]');
    }

  });

};

src/RecordNotUniqueException.js — 'cause RecordNotUniqueException isn’t included in the exceptions that directus passes down through { exceptions }

// @SOURCE: node_modules/directus/dist/exceptions/database/record-not-unique.js
import { BaseException } from '@directus/shared/exceptions';
class RecordNotUniqueException extends BaseException {
    constructor(field, extensions) {
        if (field) {
            super(`Field "${field}" has to be unique.`, 400, 'RECORD_NOT_UNIQUE', extensions);
        }
        else {
            super(`Field has to be unique.`, 400, 'RECORD_NOT_UNIQUE', extensions);
        }
    }
}
export { RecordNotUniqueException };

[Natetronn]

We created a gist explaining how we did something similar using Flows:

https://gist.github.com/Natetronn/9bafd1722a2b67b9bf4528e45992baa2

Note: it may be a bit rough around the edges and probably not the best method of achieving this, but perhaps it can be of use to others, just the same.

[rijkvanzanten]

Heya! Thanks for opening this feature request!

This feature request has received over 15 votes from the community. This means we'll move this feature request to the Under Review state!

The Core team will schedule a meeting to review this request as soon as possible. The discussion will then be approved or denied. You may or may not be invited to join this meeting with the core team.

For more information, see our Feature Request Process.

[realjjaveweb]

Sounds great! 😊 is there an approximate release date ? 🙂

[rijkvanzanten]

Hard to estimate seeing it's such a big project! Keep an eye on https://github.com/directus/directus/issues/19762 though for all updates on that front 👍🏻

[divStar]

#19762 sounds like a monumental task (judging by the experience I have myself as a software engineer). I suppose it needs to be completed before we see any progress on composite IDs / keys?

I just have a collection where I have a position, a skill and a skill-level and I'd like to disallow adding new elements if the former two are already present in the collection (regardless of the value of the third field) and I am a bit puzzled, that this feature is missing while many other highly complex features are actually present AND working flawlessly.

[rijkvanzanten]

https://github.com/directus/directus/issues/19762 sounds like a monumental task (judging by the experience I have myself as a software engineer). I suppose it needs to be completed before we see any progress on composite IDs / keys?

It is, but refactoring the current data pipeline to support composite keys is similarly a herculean task, hence my preference of including it in the new abstraction we're building rather then trying to spend all that effort on it in the current setup and then completely replacing that right after 🙂

and I am a bit puzzled, that this feature is missing while many other highly complex features are actually present AND working flawlessly.

Fair enough! That mostly stems from Directus not having those other highly complex features in the past either. The original API design really stems from that /:table / /:table/:id paradigm, where you only really have a single primary key just by nature of REST API design. In 2024 that's completely different!

[realjjaveweb]

@rijkvanzanten I see you're a person on the right place, if we get to see composite keys support some day in relatively near future (like a year or so max), I totally agree that it makes sense to only do it in the new abstraction and don't waste time on the old one 🙂

[u12206050]

As a simple workaround we have created key constraints on two columns being unique via a migration. This does throw and error in the admin dashboard if we attempt to create a record with two keys that are already paired.

// migrations/20221010-links-composite.ts

import { Knex } from 'knex';

export async function up(knex: Knex) {
  await knex.schema.table('links', table => {
    table.unique(['person', 'other']);
  });
}

export async function down(knex: Knex) {
  await knex.schema.table('links', table => {
    table.dropUnique(['person', 'other']);
  });
}