Describe the Bug

It's possible to have a column in a table that references a column that is not a primary key in another table, however Directus ignores the column being referenced and always uses the primary key.

To Reproduce

1. Import the following into a database:

create table table_a
(
  id uuid not null primary key default gen_random_uuid(),
  foo uuid unique
);

create table table_b
(
  id        uuid not null primary key default gen_random_uuid(),
  something uuid not null references table_a (foo)
);

2. Startup Directus and go to Settings -> Data Model and import Table A.

3. Import Table B, opening the configuration page for the something field and navigating to the Relationship tab. You'll see that Directus thinks that the Related Collection (table_a) field is id when it should be foo.

4. Set the Interface -> Many to One -> Display Template value to {{foo}}, and the Display -> Related Values -> Display Template value to {{foo}}.

5. Insert an entry into Table A.

6. Attempt to insert an entry into Table B selecting the item from Table A as the value for Something

7. Click save, and be given the following error:

{
  "message": "Invalid foreign key for field \"something\" in collection \"table_b\".",
  "extensions": {
    "code": "INVALID_FOREIGN_KEY",
    "collection": "table_b",
    "field": "something"
  }
}

Directus Version

v10.9.3

Hosting Strategy

Self-Hosted (Docker Image)