Constraint violation on many to many join table

[melohagan]

Hosting

• Self
  • Method: docker compose
  • Budibase Version: 2.1.41
  • App Version: 2.1.41

Describe the bug
Consider the following setup: Many employees -> Many skills
Two employees have many skills. Some overlap, some don't.
Try to change an employees skills through an auto-generated screen will result in constraint violation on the join table.

To Reproduce
Steps to reproduce the behavior:

1. Setup an Employee, Skills and Join table for a many to many relationship in Postgres. (scripts below)
2. Fetch tables in Budibase
3. Setup relationships (Many -> Many)
4. Create auto-generated screens for the Employee table
5. Preview app, and unselect a skill for an employee and save
6. See error

Expected behavior
A clear and concise description of what you expected to happen.

Screenshots
Remove 'Docker' and save

SQL Scripts

CREATE TABLE IF NOT EXISTS public."Employee"
(
    id integer NOT NULL,
    name text COLLATE pg_catalog."default",
    CONSTRAINT "Employee_pkey" PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Employee"
    OWNER to postgres;

INSERT INTO public."Employee" ("id", "name") VALUES (1, 'Alice');
INSERT INTO public."Employee" ("id", "name") VALUES (2, 'Bob');

CREATE TABLE IF NOT EXISTS public."Skills"
(
    id integer NOT NULL,
    name text COLLATE pg_catalog."default",
    CONSTRAINT "Skills_pkey" PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."Skills"
    OWNER to postgres;

INSERT INTO public."Skills" ("id", "name") VALUES (1, 'Docker');
INSERT INTO public."Skills" ("id", "name") VALUES (2, 'Microservices');
INSERT INTO public."Skills" ("id", "name") VALUES (3, 'Kubernetes');
INSERT INTO public."Skills" ("id", "name") VALUES (4, 'Spring');

CREATE TABLE IF NOT EXISTS public."jt_employee_skills_Skills_employee"
(
    employee_id integer,
    skills_id integer,
    id integer NOT NULL,
    CONSTRAINT "jt_employee_skills_Skills_employee_pkey" PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."jt_employee_skills_Skills_employee"
    OWNER to postgres;

insert into public."jt_employee_skills_Skills_employee" ("id", "employee_id", "skills_id") VALUES (1, 1, 1);
insert into public."jt_employee_skills_Skills_employee" ("id", "employee_id", "skills_id") VALUES (2, 1, 2);
insert into public."jt_employee_skills_Skills_employee" ("id", "employee_id", "skills_id") VALUES (3, 1, 3);
insert into public."jt_employee_skills_Skills_employee" ("id", "employee_id", "skills_id") VALUES (4, 2, 2);
insert into public."jt_employee_skills_Skills_employee" ("id", "employee_id", "skills_id") VALUES (5, 2, 3);
insert into public."jt_employee_skills_Skills_employee" ("id", "employee_id", "skills_id") VALUES (6, 2, 4);

[mike12345567]

Found the source of this issue - the joining table here is relatively poorly defined, the issue is that the id primary key of it isn't auto-generated, and ideally for a joining table your primary key should be a composite of the the two foreign keys, a good definition for the junction table would look like:

CREATE TABLE IF NOT EXISTS public."jt_employee_skills_Skills_employee"
(
    employee_id integer NOT NULL,
    skills_id integer NOT NULL,
    CONSTRAINT fkEmployeeId FOREIGN KEY(employee_id) REFERENCES Employee(id),
    CONSTRAINT fkSkillId FOREIGN KEY(skills_id) REFERENCES Skills(id),
    PRIMARY KEY (employee_id, skills_id)
)
WITH (
    OIDS = FALSE
)

This is the type of junction table that Budibase is designed to handle, auto-generating not null primary key fields, of any type, could be very difficult as the id field on the junction table could be a number, a string, a date etc.

[pankajjangid05]

Hi @mike12345567

I tried the same thing but still, it's not working. So basically I want to remove skills using budibase design and update the employee records. The employee records are getting updated and the new skills are also being added to the DB. The problem is that when I try to remove skills, it shows removed in the form UI, but the same is not updated in the database.

Please let me know if you want screenshots, schema, or anything else for your reference.

[mike12345567]

Hi @pankajjangid05 - there was another underlying issue which I've fixed in my PR - this will be released in the next Budibase update.

[pankajjangid05]

Hi @mike12345567

I checked the new release, but it is not working for me as expected, If you have any idea what could be the other way to do this Please let me know.

[mike12345567]

Hi @pankajjangid05 - this has not yet been released - it will be part of our main release which should occur sometime around next week. It will be in version v2.2.X. Patch versions like 2.1.<patch number> are for very minor bug fixes which don't go through QA.