Foreign key not detected in tables object

[matndev]

I'm having an issue with foreign key detection. I'm creating two tables, let's call them "Table1" and "Table2". Table1 has a primary key called "PK_Table1" on a column named "Column1". Table2 has a foreign key "FK_Table2_Column1" that references "Column1" in "Table1".

When I create the tables and inspect the resulting objects, "Table1" correctly shows "Column1" as having a primary key, but "Table2" does not show "Column1" as having a foreign key. Additionally, when I try to access the "foreignkey" property of "Column1" in "Table2", it doesn't exist.

I've checked the "checks" property of "Table2", and the foreign key constraint appears to be correctly detected and stored there (fk is equal to true and the name of the foreign key is printed, but not the columnid).

Could you please advise on how to properly detect and access foreign keys?

CREATE TABLE Table1 (
    Column1 NUMERIC(2,0) NOT NULL,
    Column2 VARCHAR(50) NOT NULL,
    Column3 DATETIME NOT NULL,
    Column4 DATETIME NOT NULL,
    Column5 VARCHAR(50) NOT NULL,
    Column6 DATETIME NOT NULL,
    CONSTRAINT PK_Table1 PRIMARY KEY (Column1),
    CONSTRAINT UC_Column1 UNIQUE (Column1),
    CONSTRAINT UC_Column3_Column4 UNIQUE (Column3, Column4)
);

CREATE TABLE Table2 (
    Column1 NUMERIC(2,0) NOT NULL,
    Column2 NUMERIC(2,0) NOT NULL,
    Column3 VARCHAR(300) NOT NULL,
    Column4 VARCHAR(50) NOT NULL,
    Column5 DATETIME NOT NULL,
    Column6 NUMERIC(4,0) NOT NULL,
    CONSTRAINT PK_Table2 PRIMARY KEY (Column1, Column2),
    CONSTRAINT UC_PK_Table2 UNIQUE (Column1, Column2),
    CONSTRAINT FK_Table2_Column1 FOREIGN KEY (Column1) REFERENCES Table1(Column1)
);

JSFiddle: https://jsfiddle.net/rcnv6gfs/

[Bidhu1024]

I can solve this issue. Please assign this bug to me.

[DarrenDsouza7273]

@Bidhu1024 can u please update if not can i work on this @mathiasrw

[DarrenDsouza7273]

can i have the documentation for it @mathiasrw

[DarrenDsouza7273]

SELECT 
    ccu.table_name AS table_name, 
    ccu.column_name AS column_name,
    ccu.constraint_name AS constraint_name,
    ccu.referenced_table_name AS referenced_table_name,
    ccu.referenced_column_name AS referenced_column_name
FROM 
    information_schema.key_column_usage AS kcu
JOIN 
    information_schema.constraint_column_usage AS ccu 
        ON ccu.constraint_name = kcu.constraint_name
WHERE 
    kcu.constraint_schema = 'your_schema'
    AND ccu.table_name = 'Table2'
    AND ccu.column_name = 'Column1';

@mathiasrw try this it will retrieve the information regarding the foreign keys

[DarrenDsouza7273]

@mathiasrw could u try this

[mathiasrw]

Looks correct to me. @matndev Does this work?

[DarrenDsouza7273]

@matndev can u update?

[DarrenDsouza7273]

@matndev any updates

[matndev]

Tried today, returns me undefined when executing SELECT in console.log.
But I would rather access fk within tables object.

alasql@4:119 Uncaught TypeError: Cannot read properties of undefined (reading 'tables')
at alasql@4:119:13221
at Array.forEach ()
at S.Select.compileDefCols (alasql@4:119:13096)
at S.Select.compile (alasql@4:49:11442)
at u.dexec (alasql@4:38:2105)
at u.exec (alasql@4:38:1789)
at u (alasql@4:31:907)

[liviaalmeida]

@DarrenDsouza7273 do you know a different way to retrieve the foreign keys from a table? I tried your solution and I got the same error as @matndev. Such as they mentioned, I would also prefer to retrieve this information from the tables object, and I'm up to make a PR for it even 🙂