SQL many-to-many breaks when through table has composite primary key

[mjashanks]

I have the tables

Customer
- customer_id : PK

Category
- category_id : PK

Customer_category
- customer_id
- category_id
PK = composite of the above 2

I have fetched the tables and setup the relationship in Budibase OK.

The relationship works when I am adding values to the relationships. I.e. checking more values in the relationship picker.

However, if I try and remove (uncheck values) - it does not save the removed values.

Now, if I add an id to my customer_category table and set it to the PK (as below) - then everything works.

- customer_id
- category_id
- id : PK

[mjashanks]

Also, its possible that I led you down the wrong path here. I was able to create the issue by the steps above - however, someone else seemed to encounter this issue and fix it themselves by recreating the relationship: #4408

[stale]

This issue has been automatically marked as stale because it has not had recent activity.

[v6admin]

Unfortunately, it's still happening to me, in multiple tables with a connection to an external postgresdb. I also tried to create several new (simple) apps with another external postgresql, mariadb and mysql db on a different host, but it still won't save removed relationship values (uncheck values in a relationship picker). It does save them.

It doesn't work in the builder backend and it doesn't work in the app front-end. I don't have this problem when using the internal bb database, there I can remove relationships.

It both happens in a self-hosted env. (via digitalocean) and your cloud solution.

**Simple Postgresql setup that doesn't save removed relationships (unchecking values in the data builder):**

-- Table: parent
---- column: Parent
---- column: child (relationship with Table Child and Column Parent)

--- Table: child
---- column: child
---- column: parent (relationship with Table Parent and Column Child

[v6admin]

I created a new database and started from scratch. Instead of using the budibase database editor, I created and updated the schemes from within pgAdmin 4. That did the trick. The relationships and the associated tables are still being created from within budibase. It seems when you create tables and relationships from budibase, the database gets invalid.

[stale]

This issue has been automatically marked as stale because it has not had recent activity.