BudiBase invalid error when creating relation for MySQL using a relationship table

[vbonachuck-sr]

Discussed in #13616

^{Originally posted by vbonachuck-sr May 5, 2024}
Hello everyone. Hope this is a straight forward question. We thought we would try building a form with budibase to maintain the some of master the tables in our MySql db as a quick internal data maintenance solution. The database is fully normalized and there are main tables and relationship tables.

master_material = has all materials
master_information = has all related information about any given material
master_material_information = has the relationship data. A given material may link to 1 or more information records

I'm getting an error in BudiBase which I cannot figure out why that is:

I believe I am matching the requirements, all non-primary key fields as nullable as per screenshot below:

What am I missing here?? Really appreciate any help I can get on this!

[linear]

BUDI-8236 BudiBase invalid error when creating relation for MySQL using a relationship table

[ConorWebb96]

Hey @vbonachuck-sr,

Do you have a MySQL schema/table create scripts for these tables, that I can look at? Doesn't need to include data the create scripts would be enough.

I created a small very simple sql script below that imitates binary as a primary key and everything seems to work fine.

-- Create table1
CREATE TABLE table1 (
    id BINARY(16) PRIMARY KEY,
    column1 TEXT,
    column2 INT
);

-- Create table2
CREATE TABLE table2 (
    id BINARY(16) PRIMARY KEY,
    column3 TEXT,
    column4 INT
);

-- Create the junction table
CREATE TABLE junction_table (
    table1_id BINARY(16),
    table2_id BINARY(16),
    PRIMARY KEY (table1_id, table2_id),
    FOREIGN KEY (table1_id) REFERENCES table1(id),
    FOREIGN KEY (table2_id) REFERENCES table2(id)
);

-- Insert data into table1
INSERT INTO table1 (id, column1, column2) VALUES (UNHEX('0123456789abcdef'), 'Data 1', 123);
INSERT INTO table1 (id, column1, column2) VALUES (UNHEX('fedcba9876543210'), 'Data 2', 456);

-- Insert data into table2
INSERT INTO table2 (id, column3, column4) VALUES (UNHEX('aaaa1111bbbb2222'), 'More Data 1', 789);
INSERT INTO table2 (id, column3, column4) VALUES (UNHEX('cccc3333dddd4444'), 'More Data 2', 101112);

-- Insert data into the junction table
INSERT INTO junction_table (table1_id, table2_id) VALUES (UNHEX('0123456789abcdef'), UNHEX('aaaa1111bbbb2222'));
INSERT INTO junction_table (table1_id, table2_id) VALUES (UNHEX('fedcba9876543210'), UNHEX('cccc3333dddd4444'));

Screenshots in Budibase

Junction table

Table 1

Table 2

Relationship

[ConorWebb96]

Budibase lacks support for primary keys of the Binary type. Consequently, executing automatic CRUD (Create, Update, Delete) operations isn't possible and you will receive "The values in the where clause must not be object or array," you saw before whenever attempts are made to modify or delete data.

We have no plans in the immediate future to support this as it's a bit of an edge case for data storage/indexing reasons in most RDBMS

I'll create a feature request for this and link it here. Additionally, if this functionality is crucial for your use case, you might want to consider reaching out to Budibase's sales team to discuss it further. They might be able to provide more insight or explore potential solutions tailored to your needs.

[vbonachuck-sr]

Hey Connor Please see attached my structures. Looking at your test, it seems mine should work too… Let me know if you find anything from what I’m sending you. I’ll try to replicate your example too Thanks Vander From: Conor Webb ***@***.***> Sent: Thursday, May 9, 2024 8:48 AM To: Budibase/budibase ***@***.***> Cc: vbonachuck-sr ***@***.***>; Mention ***@***.***> Subject: Re: [Budibase/budibase] BudiBase invalid error when creating relation for MySQL using a relationship table (Issue #13646) Hey @vbonachuck-sr <https://github.com/vbonachuck-sr> , Do you have a MySQL schema/table create scripts for these tables, that I can look at? Doesn't need to include data the create scripts would be enough. I created a small very simple sql script below that imitates binary as a primary key and everything seems to work fine.

…

-- Create table1 CREATE TABLE table1 ( id BINARY(16) PRIMARY KEY, column1 TEXT, column2 INT );

-- Create table2 CREATE TABLE table2 ( id BINARY(16) PRIMARY KEY, column3 TEXT, column4 INT );

-- Create the junction table CREATE TABLE junction_table ( table1_id BINARY(16), table2_id BINARY(16), PRIMARY KEY (table1_id, table2_id), FOREIGN KEY (table1_id) REFERENCES table1(id), FOREIGN KEY (table2_id) REFERENCES table2(id) );

-- Insert data into table1 INSERT INTO table1 (id, column1, column2) VALUES (UNHEX('0123456789abcdef'), 'Data 1', 123); INSERT INTO table1 (id, column1, column2) VALUES (UNHEX('fedcba9876543210'), 'Data 2', 456);

-- Insert data into table2 INSERT INTO table2 (id, column3, column4) VALUES (UNHEX('aaaa1111bbbb2222'), 'More Data 1', 789); INSERT INTO table2 (id, column3, column4) VALUES (UNHEX('cccc3333dddd4444'), 'More Data 2', 101112);

-- Insert data into the junction table INSERT INTO junction_table (table1_id, table2_id) VALUES (UNHEX('0123456789abcdef'), UNHEX('aaaa1111bbbb2222')); INSERT INTO junction_table (table1_id, table2_id) VALUES (UNHEX('fedcba9876543210'), UNHEX('cccc3333dddd4444')); Screenshots in Budibase Junction table Screenshot.2024-05-09.at.13.43.19.png (view on web) <https://github.com/Budibase/budibase/assets/126772285/fb6c858c-bfb9-45be-8f28-0b5cb38f867e> Table 1 Screenshot.2024-05-09.at.13.43.39.png (view on web) <https://github.com/Budibase/budibase/assets/126772285/74439c7c-ffab-473f-af62-9c075341ee53> Table 2 Screenshot.2024-05-09.at.13.44.05.png (view on web) <https://github.com/Budibase/budibase/assets/126772285/8c36667d-7535-4290-bfbe-35fb593cd6f5> Relationship Screenshot.2024-05-09.at.13.44.23.png (view on web) <https://github.com/Budibase/budibase/assets/126772285/35289472-c0b7-4152-951a-a7510d0ea70d> — Reply to this email directly, view it on GitHub <#13646 (comment)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/BF332Y7LXXKG7ZTUFNUKIRDZBNV7LAVCNFSM6AAAAABHOWFJR6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMBSGU4TMMRRGA> . You are receiving this because you were mentioned. <https://github.com/notifications/beacon/BF332Y2NDWFF52AANPSBIM3ZBNV7LA5CNFSM6AAAAABHOWFJR6WGG33NNVSW45C7OR4XAZNMJFZXG5LFINXW23LFNZ2KUY3PNVWWK3TUL5UWJTT5KMJHE.gif> Message ID: ***@***.*** ***@***.***> >