sqlite

Fix SQLite JOIN Query Without Result

I’m developing an API and using SQLite as my database and here is my table creation along with the relationship.

CREATE TABLE IF NOT EXISTS superhero (
    superhero_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    gender TEXT
);

CREATE TABLE IF NOT EXISTS address (
    address_id INTEGER PRIMARY KEY AUTOINCREMENT,
    street TEXT,
    city TEXT,
    country TEXT,
    superhero_id INTEGER,
    FOREIGN KEY(superhero_id) REFERENCES superhero(superhero_id) ON DELETE CASCADE ON UPDATE CASCADE 
);

CREATE TABLE IF NOT EXISTS power (
    power_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

CREATE TABLE IF NOT EXISTS superheropower (
    power_id INTEGER,
    superhero_id INTEGER,

    FOREIGN KEY(power_id) REFERENCES power(power_id) ON DELETE CASCADE ON UPDATE CASCADE ,
    FOREIGN KEY(superhero_id) REFERENCES superhero(superhero_id) ON DELETE CASCADE ON UPDATE CASCADE 
);

But I got very weird issue that when I execute SQL query with JOIN, I always got no result. But it’s working with simple SELECT query.

After googling, I just know that SQLite is disabled the JOIN ability by default.
So we need to enable it when we create the table.
What you need to do, add this following syntax before your table creation.

PRAGMA foreign_keys = ON;

Reference: https://sqlite.org/foreignkeys.html#fk_enable