How to remove virtual relationships

Schema & Data
1

Hi, I tried to test out adding virtual relationships between tables, but it seems I cannot remove a relationship once it’s set? I tried clicking on the relationship in the Relationships section in the Schema and have no option to delete it. If I change it in any way, I get an error “Syntax error or access violation: 1068 Multiple primary key defined…”, and when I click on the field itself, the Foreign and Virtual keys are already set so I cannot change or add multiple relationships.

So I have two questions, how do I remove virtual relationships and how do I add more than one relationship using the same field to reference different tables?

2

Hi @filiptod,

I think the best way for you to do this now is through the SQL REST API (API Docs)

DELETE /your-db-service/_schema/{table_name}/_related/{relationship_name}

You can also create new virtual relationships through the REST API.

POST /your-db-service/_schema/{table_name}/_related

{
  "resource": [
   {
      "alias": null,
      "name": "name_by_table_id",
      "label": "Name By Table Id",
      "description": null,
      "native": [],
      "type": "has_many",
      "field": "id",
      "is_virtual": true,
      "ref_service_id": 123,
      "ref_table": "name",
      "ref_field": "table_id",
      "ref_on_update": null,
      "ref_on_delete": null,
      "junction_service_id": null,
      "junction_table": null,
      "junction_field": null,
      "junction_ref_field": null,
      "always_fetch": false,
      "flatten": false,
      "flatten_drop_prefix": false
    }
  ]
}

You can also query all virtual and non-virtual relationships in a table.

GET /your-db-service/_schema/{table_name}/_related

best regards,
Junior Conte

1 Like
3

Thank you very much for the reply! I went to the API Docs and all I seem to have access to is DELETE /[my_service]/_schema/{table_name}/{field_name}. There’s no API that adds a _related/{relationship_name}. I tried deleting the virtual relationship using its name as the field_name, it says it was successful but the relationship was still there, so that didn’t work. Could this be due to using an older version of Dreamfactory or some service that I don’t have?

4

In my case, I did the tests with versions 2.4, 2.5 and 2.6.
Which version are you using?

5

any update for this issue? I experienced same problem here, cannot delete virtual relationship even from API Docs. It said success but nothing happened. I am using version 2.7.0