MSSQL many to many relationships with spaces between the foreign keys throw an error when trying to related records

[ConorWebb96]

Checklist

• [x] I have searched budibase discussions and github issues to check if my issue already exists

Hosting

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

Describe the bug After creating a many to many relationship between 2 MS SQL tables that have primary keys with spaces in their names. Once you try to relate records you will constantly get hit with: The INSERT statement conflicted with the FOREIGN KEY constraint.. This error isn't really helpful. The issue lies with the spaces in the column names. If i alter the primary key names + FOREIGN key names in the bridge table and re-try everything works.

To Reproduce Steps to reproduce the behavior:

1. Create 3 tables using the sql create script below
2. Fetch these tables in Budibase
3. Try and change one of the relationships in the data area
4. See error

Expected behavior You should be allowed to related records through Budibase with foreign key columns that have spaces in the names. Alternatively a better error may need thrown, as the error suggests that the rows you are trying to link don't exist. Which i guess is correct depending on how the query is built.

Create scripts

-- Create TableA
CREATE TABLE TableA (
    [Primary Key A] INT PRIMARY KEY,
    [ColumnA] NVARCHAR(50)
);

-- Create TableB
CREATE TABLE TableB (
    [Primary Key B] INT PRIMARY KEY,
    [ColumnB] NVARCHAR(50)
);

-- Create TableAB as the bridge table
CREATE TABLE TableAB (
    [Primary Key A] INT,
    [Primary Key B] INT,
    PRIMARY KEY ([Primary Key A], [Primary Key B]),
    FOREIGN KEY ([Primary Key A]) REFERENCES TableA([Primary Key A]),
    FOREIGN KEY ([Primary Key B]) REFERENCES TableB([Primary Key B])
);

-- Insert rows into TableA
INSERT INTO TableA ([Primary Key A], [ColumnA]) VALUES (1, 'ValueA1');
INSERT INTO TableA ([Primary Key A], [ColumnA]) VALUES (2, 'ValueA2');
INSERT INTO TableA ([Primary Key A], [ColumnA]) VALUES (3, 'ValueA3');

-- Insert rows into TableB
INSERT INTO TableB ([Primary Key B], [ColumnB]) VALUES (10, 'ValueB1');
INSERT INTO TableB ([Primary Key B], [ColumnB]) VALUES (20, 'ValueB2');
INSERT INTO TableB ([Primary Key B], [ColumnB]) VALUES (30, 'ValueB3');

-- Insert rows into TableAB to establish many-to-many relationships
INSERT INTO TableAB ([Primary Key A], [Primary Key B]) VALUES (1, 10);
INSERT INTO TableAB ([Primary Key A], [Primary Key B]) VALUES (1, 20);
INSERT INTO TableAB ([Primary Key A], [Primary Key B]) VALUES (2, 20);
INSERT INTO TableAB ([Primary Key A], [Primary Key B]) VALUES (2, 30);
INSERT INTO TableAB ([Primary Key A], [Primary Key B]) VALUES (3, 10);
INSERT INTO TableAB ([Primary Key A], [Primary Key B]) VALUES (3, 30);

[linear[bot]]

BUDI-8548 MSSQL many to many relationships with spaces between the foreign keys throw an error when trying to related records