Composite unique..ness

[David_O]

Hiya!

I’m modelling a Directus collection calledcustomersas a join table between:

• directus_users
• accounts

Each row represents “this user is a customer of this account”. What I need in the customer collection is:

• account- required
• user- required
• a user can be a customer of many accounts
• an account can have many customers
• but the (account, user) pair must be unique

I initially marked both fields as unique, but that was wrong because it prevents:
• multiple customers on one account
• one user being a customer of multiple accounts

What I actually need is a composite unique constraint/index onaccount + user

My question:
• can this be created fully through Directus schema/data-model tools
• or does it currently require adding the constraint at the database level?

If it is supported in Directus:
• where in the UI is that configured?

If not:
• what is the recommended Directus-friendly approach for this kind of join-table uniqueness?

I’m trying to keep all schema management inside Directus rather than applying manual Postgres constraints where possible.

Thanks in advance for you thoughts!

Dave

[maazamaani]

Hi
I think you are trying to make a collection unique not by a single field, but by 2 fields. this is not achievable in directus data studio, but you can do it directly in your database and directus will follow that. (when you add a row with same field1 and field2 values, directus returns that field1 and field2 should be unique)

in your db do this:

ALTER TABLE your_table_name
ADD CONSTRAINT unique_two_fields
UNIQUE (field1, field2);

[alexv-directus]

Hi David. When you create an m2m field, you can specify if duplicates should be allowed. This is purely for the Directus studio and does not add any composite constraints at the DB level, but it may be sufficient for your use case?

Alternatively, you can use a filter flow which can check for existing customer / user combinations and throw an error if the user already exists.