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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s