Not all tables get deleted, esp m:m, 1:m etc

Help
1

Hi, folks!

I am developing and testing Directus and build lots of collections with various relations. Often I delete a relation again – I assume this happens often in planning stage.

But it seems those tables and fields are not actually deleted. When I create a new relation and use the same names, I get an error message, when saving, saying the field already exists and has a relation.

I am sure I am doing something wrong. There is a section called “Relational Trigger”, where all the option are now set to “Nullify …”. And I think this is what I want, otherwise, whenever a user deletes a file or item, bad things would happen to the DB.

Is there a way to figure out, which tables and fields are unused in my Directus project, so I can really delete them? Currently I have the impression that developing could leave lots of “garbage” behind the DB…

5

Just stumbled across this beauty. So much good stuff lingering in the dark.

It’s available on the marketplace, but it’s not sandboxed, so you need to set MARKETPLACE_TRUST=all to show it on the marketplace.

Although not showing the exact join table name, it might help you better understand the data schema from within Data Studio.

2

Hello,

This is happening because of the cache, it will be best to disable the caching while you are in the planing stage. Set CACHE_ENABLED as false

3

It seems that caching is not the reason for that. Here is my docker-compose.yaml file:

version: '3'

services:
  directus_jvds:
    image: directus/directus:11.9.3
    ports:
      - 8056:8055
    volumes:
      - ./uploads:/directus/uploads
      - ./database:/directus/database
      - ./extensions:/directus/extensions
    environment:
      KEY: ''
      SECRET: ''
      DB_HOST: 'localhost'
      DB_CLIENT: 'sqlite3'
      DB_FILENAME: './database/data.db'
      ADMIN_EMAIL: ''
      ADMIN_PASSWORD: ''
      PUBLIC_URL: 'https://...:8056'
      STORAGE_LOCATIONS: 'local'
      STORAGE_LOCAL_DRIVER: 'local'
      STORAGE_LOCAL_ROOT: './uploads'
      WEBSOCKETS_ENABLED: true

      CORS_ENABLED: true
      CORS_ORIGIN: http://localhost
      CORS_METHODS: GET,POST,PATCH,DELETE
      CORS_ALLOWED_HEADERS: Content-Type,Authorization

      REDIS_ENABLED: false
      CACHE_ENABLED: false

Do I maybe have to change some other configuration?
This Directus instance is hosted on DigitalOcean – maybe I have to change some settings there?

4

Since it might not be an cache issue, it might just be an issue of orphaned collections or FK constraints. Directus magically creates join tables and foreign key constraints when adding relational interfaces, but cleaning those up might require additional steps.

I was too lazy to write a removal guide on my own, so I asked ChatGPT to do it for me. It basically looks good to me, but let me know if you encounter any failures.

Even if that doesn’t solve this problem, it may serve others as reference. It may help you tho, identifying orphan collections, based on the naming convention Directus uses when creating join tables.

Additional remark: The “Relational Trigger” you mentioned, just affects how referential integrity is handled on the join table rows while the relation is in effect. It doesn’t delete the join table or alter join table rows when dropping the interface.

:broom: Removing Relationships in Directus (Data Modeling Phase)

If you’re still designing your schema in Directus Data Studio and decide to remove relationships — such as many-to-many, many-to-any, one-to-many, or many-to-one — it’s important to follow the correct deletion order:

:counterclockwise_arrows_button: Always delete relationship fields first, then delete any join collections.

To illustrate each type, we’ll consistently use the following collections:

  • countries
  • cities
  • mountains
  • countries_cities (M2M join collection)
  • countries_points_of_interest (M2A polymorphic join collection)

:repeat_button: Many-to-Many (M2M): countries ↔ cities (via countries_cities)

Structure

  • countries and cities are linked by a many-to-many relationship.
  • Directus automatically creates a join collection: countries_cities.

Removal Steps

  1. Go to the countries collection.
  2. Delete the relationship field pointing to cities.
  3. Go to cities.
  4. Delete the inverse relationship field pointing to countries, if added.
  5. Go to the countries_cities collection.
  6. Delete the collection itself.

:white_check_mark: M2M is fully removed.

:cyclone: Many-to-Any (M2A): countries, cities, mountains → countries_points_of_interest

Structure

  • Each of the collections (countries, cities, mountains) uses a many-to-any field called points_of_interest.
  • The field is originally created in countries, so Directus names the join collection: countries_points_of_interest.

This is useful when multiple resource types (like countries, cities, and mountains) can reference shared items (e.g., tourist attractions).

Removal Steps

  1. Go to the countries collection.
  2. Delete the points_of_interest field.
  3. Go to the cities collection.
  4. Delete the points_of_interest field, if added.
  5. Go to the mountains collection.
  6. Delete the points_of_interest field, if added.
  7. Go to the countries_points_of_interest collection.
  8. Delete the collection.

:white_check_mark: M2A and its polymorphic join table are fully removed.

:link: One-to-Many (O2M): countries → cities

Structure

  • Each city belongs to one country.
  • cities has a foreign key field (e.g., country_id).
  • countries may have a virtual field (e.g., cities) to show the related records.

Removal Steps

  1. Go to the cities collection.
  2. Delete the foreign key field (country_id).
  3. Go to the countries collection.
  4. Delete the virtual field (cities), if present.

:white_check_mark: O2M is now fully removed.

:repeat_button: Many-to-One (M2O): cities → countries

Structure

  • Each city references a single country — the inverse of the O2M relationship.
  • cities has a field like country_id.
  • countries may show related cities in a virtual field.

Removal Steps

  1. Go to the cities collection.
  2. Delete the country_id field.
  3. Go to the countries collection.
  4. Delete the virtual field (cities), if present.

:white_check_mark: M2O is also removed — same structure as O2M but viewed from the reverse direction.

:white_check_mark: Summary Table

Relationship Type Example Step 1: Delete Fields Step 2: Delete Collection
Many-to-Many countries ↔ cities Relationship fields in countries and cities countries_cities
Many-to-Any countries, cities, mountains → shared points_of_interest fields in all three collections countries_points_of_interest
One-to-Many countries → cities country_id in cities, cities virtual field in countries (no join collection)
Many-to-One cities → countries country_id in cities, cities virtual field in countries (no join collection)
6

I think I faced the same issue you are having with a sqlite db as well, as far as I remember I solved that by accessing the database directly and deleted the field from the directus_fields collection.