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

Author: arximboldiCreated Dec 15, 2023Updated Sep 10, 2026

LabelsHelp wantedRelated to: SQL complianceBug: Code provided to reproducedGood first issue

Example code:

sql

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:

sql

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

Source: AlaSQL/alasql

View original on GitHub View discussion on GitHub