Alasql: Error in exception message for foreign key violation error

[zachsa]

When trying to insert a row into table B that references table A, if the referenced primary key in table A doesn't exist then alasql throw an exception. But the name of the table with the missing primary key is shown as undefined in the message:

C:\<path>\node_modules\alasql\dist\alasql.fs.js:12796
Error: Foreign key "20" is not found in table undefined

[mathiasrw]

Hi @zachsa - Really glad you are reaching out so we can make a better library.

Is it because it would make more sense if it stated Error: Foreign key "20" is not found in table not defined?

[zachsa]

Hi. The table is defined. If I run the same insert statement with, instead of 20, an id of 2 then it succeeds.

When I read the message I see it as saying undefined instead of the table name.

[zachsa]

Or, I think it would make sense if it said "row undefined" or something along that line (specifying that the requested row is undefined).

Or just "foreign key violation".

[Rudy167]

error message can be changed by editing the file createtable.js(the given function)

@zachsa can you please tell the exact sequence of queries that you used which resulted in the "undefiend" name for corresponding table name?

[zachsa]

Hi @Rudy167 - sorry it's been quite a while and I don't have this code anymore. If I remember correctly (and I might not be...), I had two tables along the lines of:

# Table1
id | someProperty

# Table2
id | someProperty | table1Id

id in both tables was an auto increment primary key constraint, and I think that table1Id was a foreign key constraint. And then I think I was just trying to insert directly into Table2

insert into Table2 (someProperty, table1Id) values ('some value', 20)

And the failed message was because Table1 didn't have a row with the ID of 20.

[Rudy167]

thanks @zachsa, i was able to debug the issue, and was able to change to "undefined value" to the corresponding table name,
example:
Foreign key "3" is not found in table Fruits
for the code i used in #1054

var alasql = require('/home/amit/Desktop/alasql-develop/dist/alasql.js');
create_string=`CREATE DATABASE Fruits;
      USE DATABASE Fruits;
      CREATE TABLE Fruits (
        fruitid INT PRIMARY KEY,
        fruitname NVARCHAR(MAX),
        price MONEY
      );

      CREATE TABLE Orders (
        orderid INT PRIMARY KEY IDENTITY,
        fruitid INT REFERENCES Fruits(fruitid),
        qty FLOAT
      );
`

alasql(create_string);

// insert_obj = {
//     fruits : [
//         {
//             "fruitid" : 1,
//             "fruitname" : "Banana",
//             "price" : 2
//         },
//         {
//             "fruitid" : 2,
//             "fruitname" : "Apple",
//             "price" : 1
//         }
//     ],
//     orders : [
//         {
//             "orderid" : 1,
//             "fruitid" : 9,
//             "qty" : 7
//         },
//         {
//             "orderid" : 1,
//             "fruitid" : 9,
//             "qty" : 7
//         }
//     ]
// }

// alasql('SELECT * INTO Fruits FROM ?',[insert_obj.fruits]);
// alasql('SELECT * INTO Orders FROM ?',[insert_obj.orders]);


alasql("INSERT INTO Fruits VALUES (1,'bababa',9)");
alasql("INSERT INTO Orders VALUES (2,3,9)");



console.log(alasql("SELECT * FROM Fruits"));

I have made the relevant changes in my forked repository please take a look @zachsa @mathiasrw and let me know if the bug still persists.

[zachsa]

Thanks @Rudy167 - I won't get around to doing this @mathiasrw since I'm not currently using this library in any projects (but I hope to use it again soon!)

[mathiasrw]

@Rudy167 any change you could provide a Pull Request with the code?

[Rudy167]

sure @mathiasrw i will do it right away, is the format of the error 'key "20"(id) is not found in table (table name)' acceptable?

[mathiasrw]

Seams perfect to me...