Foreign / Primary Key Validation

[akumadare]

Hi, Whilst looking at bug #1009 i tried to replicate but couldnt. When i checked further I noticed that neither foreign key integrity nor primary key uniqueness is validated. It is possible that my code syntax is wrong!

const alasql = require('alasql');

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]);

console.log(alasql("SELECT * FROM Orders ORDER BY name DESC"));

Output:

[ { orderid: 1, fruitid: 9, qty: 7 },
  { orderid: 1, fruitid: 9, qty: 7 } ]

Should this not error as I am both duplicating primay keys and using an invalid foreign key?

[mathiasrw]

Sounds like a clear bug (or missing feature)

In any case, it should be very clear from the documentation if this is not implemented on purpose.

[akumadare]

From bug #1009 it sound like this feature used to work. Looking at the code there is a function that verifies foreign keys / primary ket violation but I dont think this is being called. I will try to have a look further.

[akumadare]

I will get hold of a release from before bug #1009 was reported to see if it used to be supported.

[mathiasrw]

Thank you @akumadare - that would be great.

[Rudy167]

@akumadare @mathiasrw The constraint are in place if you use sql like syntax, try

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"));

while bug #1009 was due to an undefined variable fktable.tableid that generated "undefined" value, thanks to @zachsa for pointing out his approach to reproduce the bug, although it would be a great idea to implement the syntax that you are suggesting @akumadare.