Foreign Key column being ignored

[kashike]

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:
```json
{
  "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)

[paescuj]

This is similar to #21592 as it is also not supported, however that as well means it shouldn't be possible to set up like this 👍