Composite "Unique" constraint

[Dominic-Marcelino]

Describe the Bug

We have a composite unique constraint on the database (created manually).

If we try to create an item on the app that violates the constraint, it logs an error 400 and ends in an empty items page form.

I know that directus doesn't support composite keys but I think the error could be catched and an error displayed. Directus at least notices that some unique constraint was violated, as on the Node console it logs an "Field has to be unique" error.

To Reproduce

1.) Create a collection with two fields
2.) Manually create a constraint that a combination of those two fields needs to be unique
3.) Create an item
4.) Try to create the same item again

Errors Shown

App: crashes, logs an error: "Failed to load resource: the server responded with a status of 400 (Bad Request)"
Node-process: Logs an error: "🐛 Field has to be unique."

What version of Directus are you using?

9.15.1

What version of Node.js are you using?

16.15

What database are you using?

MySql

What browser are you using?

Chrome

How are you deploying Directus?

N/A

[azrikahar]

I know that directus doesn't support composite keys but I think the error could be catched and an error displayed.

This was actually added! However it seems like it's not working for MySQL since field was never returned (to display what fields has the unique constraint), as described here: #15033 (comment)

As it's never returned, it's causing the .endsWith() usage here to crash when used on a null value:

directus/app/src/components/v-form/v-form.vue

Line 63 in 0bffb2b

(err.field === fieldName || err.field.endsWith(`(${fieldName})`))

Error response for MySQL (missing field):

{
  "errors": [
    {
      "message": "Field has to be unique.",
      "extensions": {
        "code": "RECORD_NOT_UNIQUE",
        "collection": "test",
        "field": null,
        "invalid": "T1-D1",
        "stack": "Error: Field has to be unique.\n    at uniqueViolation (C:\\Users\\Azri\\Documents\\Projects\\directus\\api\\src\\exceptions\\database\\dialects\\mysql.ts:66:10)\n    at extractError (C:\\Users\\Azri\\Documents\\Projects\\directus\\api\\src\\exceptions\\database\\dialects\\mysql.ts:22:11)\n    at translateDatabaseError (C:\\Users\\Azri\\Documents\\Projects\\directus\\api\\src\\exceptions\\database\\translate.ts:25:24)\n    at C:\\Users\\Azri\\Documents\\Projects\\directus\\api\\src\\services\\items.ts:134:39\n    at processTicksAndRejections (node:internal/process/task_queues:96:5)"
      }
    }
  ]
}

Error response for Postgres (has field):

{
  "errors": [
    {
      "message": "Field \"title, description\" has to be unique.",
      "extensions": {
        "code": "RECORD_NOT_UNIQUE",
        "collection": "test",
        "field": "title, description",
        "invalid": "T1, D1",
        "stack": "Error: Field \"title, description\" has to be unique.\n    at uniqueViolation (C:\\Users\\Azri\\Documents\\Projects\\directus\\api\\src\\exceptions\\database\\dialects\\postgres.ts:46:9)\n    at extractError (C:\\Users\\Azri\\Documents\\Projects\\directus\\api\\src\\exceptions\\database\\dialects\\postgres.ts:20:11)\n    at translateDatabaseError (C:\\Users\\Azri\\Documents\\Projects\\directus\\api\\src\\exceptions\\database\\translate.ts:29:27)\n    at C:\\Users\\Azri\\Documents\\Projects\\directus\\api\\src\\services\\items.ts:134:39\n    at processTicksAndRejections (node:internal/process/task_queues:96:5)"
      }
    }
  ]
}

This is how it looks like in Postgres for example:

---

I believe this is shares the same root cause as #15033 (and it's also on MySQL), so let's track this issue over there.

[azrikahar]

Granted this shouldn't be taken as an official solution as this logic is technically subject to change unless stated otherwise, you can try to name the constraint as <collection>_<field>_unique as described here:

directus/api/src/exceptions/database/dialects/mysql.ts

Lines 46 to 50 in 0bffb2b

	/**
	* MySQL's error doesn't return the field name in the error. In case the field is created through
	* Directus (/ Knex), the key name will be `<collection>_<field>_unique` in which case we can pull
	* the field name from the key name
	*/

Thus for my example in the above comment, naming it as test_title_description_unique in MySQL yields the following result. Still not ideal, but can be a workaround in the mean time:

[Dominic-Marcelino]

Thus for my example in the above comment, naming it as test_title_description_unique in MySQL yields the following result. Still not ideal, but can be a workaround in the mean time:

Thanks for the workaround, that works for now! :)