search

AlaSQL / alasql

AlaSQL.js - JavaScript SQL database for browser and Node.js. Handles both traditional relational tables and nested JSON data (NoSQL). Export, store, and import data from localStorage, IndexedDB, or Excel.
http://alasql.org
MIT License
7.02k stars 656 forks source link

Foreign key via `REFERENCES` does not work with `FILESTORAGE` #1846

Open arximboldi opened 9 months ago

arximboldi commented 9 months ago

Example code:

CREATE TABLE IF NOT EXISTS banks (
       name STRING PRIMARY KEY,
       is_open BOOLEAN DEFAULT true,
       [key] STRING
);

CREATE TABLE IF NOT EXISTS pigs (
       id STRING PRIMARY KEY,
       bank STRING REFERENCES banks(name)
       ready BOOLEAN,
       dream STRING,
       notes STRING,
       kind STRING,
       timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

Result:

/home/raskolnikov/dev/cerditos/server/node_modules/alasql/dist/alasql.fs.js:12518
                    if (fktable.pk.columns && fktable.pk.columns.length > 0) {
                                   ^

TypeError: Cannot read properties of undefined (reading 'columns')
    at /home/raskolnikov/dev/cerditos/server/node_modules/alasql/dist/alasql.fs.js:12518:21
    at Array.forEach (<anonymous>)
    at yy.CreateTable.execute (/home/raskolnikov/dev/cerditos/server/node_modules/alasql/dist/alasql.fs.js:12452:11)
    at adrunone (/home/raskolnikov/dev/cerditos/server/node_modules/alasql/dist/alasql.fs.js:4811:16)
    at FS.createTable (/home/raskolnikov/dev/cerditos/server/node_modules/alasql/dist/alasql.fs.js:18892:10)
    at yy.CreateTable.execute (/home/raskolnikov/dev/cerditos/server/node_modules/alasql/dist/alasql.fs.js:12662:38)
    at adrunone (/home/raskolnikov/dev/cerditos/server/node_modules/alasql/dist/alasql.fs.js:4811:16)
    at alasql.adrun (/home/raskolnikov/dev/cerditos/server/node_modules/alasql/dist/alasql.fs.js:4819:2)
    at alasql.dexec (/home/raskolnikov/dev/cerditos/server/node_modules/alasql/dist/alasql.fs.js:4731:11)
    at alasql.exec (/home/raskolnikov/dev/cerditos/server/node_modules/alasql/dist/alasql.fs.js:4664:17)

Node.js v18.18.2

I have tried with LOCALSTORAGE instead and the forementioned code seems to work. I don't think that foreign key rules are actually being applied though.

This alternative syntax seems to be working, but also it seems that the constraint is simply ignored:

CREATE TABLE IF NOT EXISTS pigs (
       id STRING PRIMARY KEY,
       bank STRING,
       ready BOOLEAN,
       dream STRING,
       notes STRING,
       kind STRING,
       timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
       FOREIGN KEY (bank) REFERENCES bank(name)
);