Does SQLite3 Not Support Foreign Key Constraints

Does SQLite3 not support foreign key constraints?

In SQLite 3.x, you have to make the following query every time you connect to an SQLite database:

PRAGMA foreign_keys = ON;

Otherwise SQLite will ignore all foreign key constraints.

Why every time? Backwards compatibility with SQLite 2.x, according to the the documentation.

In SQLite 4.x, FK constraints will be enabled by default.

Does SQLite3 not support foreign key constraints?

In SQLite 3.x, you have to make the following query every time you connect to an SQLite database:

PRAGMA foreign_keys = ON;

Otherwise SQLite will ignore all foreign key constraints.

Why every time? Backwards compatibility with SQLite 2.x, according to the the documentation.

In SQLite 4.x, FK constraints will be enabled by default.

Foreign key constraint does not apply

Foreign key constraints are disabled by default as it is explained here: SQLite Foreign Key Support.

To enable them execute first:

PRAGMA foreign_keys = ON;

See the demo.

Enabling Foreign key constraints in SQLite

Finally figured this out from this post. The PRAGMA foreign_key setting does not persist but you can set it every time the connection is made in the ConnectionString. This allows you to use Visual Studio's table adapters.

1. Make sure you have the latest version (1.0.73.0) of system.data.sqlite installed (1.0.66.0 will not work).
2. Change your ConnectionString to data source=C:\Dbs\myDb.db;foreign keys=true; (replace C:\Dbs\myDb.db with your sqlite database).

SQLite Foreign keys not working correctly

The documentation says:

Foreign key constraints must be enabled by the application at runtime, using the PRAGMA foreign_keys command. For example:

sqlite> PRAGMA foreign_keys = ON;

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.

Foreign Key constraint is not working properly on SQLite

The problem is this:

ON DELETE SET DEFAULT

with the definition of the foreign keys in games, because the default value for all of them is defined to be 0 which (I suspect) does not correspond to a valid id of the corresponding referenced tables.

What you can do is define the foreign keys as nullable and with ON DELETE SET NULL like this:

CREATE TABLE IF NOT EXISTS games (
  g_id INTEGER NOT NULL,
  g_name TEXT NOT NULL,
  g_genre INTEGER, -- nullable
  g_pub INTEGER, -- nullable
  year_released INTEGER NOT NULL,
  original_platform INTEGER, -- nullable
  PRIMARY KEY(g_id),
  FOREIGN KEY(g_genre) REFERENCES game_genre(genre_id) ON DELETE SET NULL ON UPDATE CASCADE,
  FOREIGN KEY(g_pub) REFERENCES game_publisher(gp_id) ON DELETE SET NULL ON UPDATE CASCADE,
  FOREIGN KEY(original_platform) REFERENCES platform(platform_id) ON DELETE SET NULL ON UPDATE CASCADE
)

Now when you delete a row from a parent table, the child key value will be set to NULL, which is allowed.

Finally, for the question:

Do I have to turn ON the foreign key constraint every time I make a
connection to the database?

the answer is yes, if you are going to use operations involving the integerity of the foreign keys, like the insertion of a new row or update a row in the child table games or the modification of rows (deletions or updates) of the parent tables.

Non-existent foreign key doesn't cause error in SQLite

From the relevant documentation:

In order to use foreign key constraints in SQLite, the library must be compiled with neither SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined. If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to version 3.6.19 (2009-10-14) - foreign key definitions are parsed and may be queried using PRAGMA foreign_key_list, but foreign key constraints are not enforced.

and:

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.

and:

Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command. For example:

sqlite> PRAGMA foreign_keys = ON;

Apparently you should use PRAGMA foreign_keys = ON at the top of your connection, and potentially rebuild with the appropriate options (though if you installed from package then I would personally assume that this has been done).

_{Source: Google sqlite foreign key, first result}

How do you enforce foreign key constraints in SQLite through Java?

When you look at the SQLite Foreign Key Support page I would interpret that

1. SQLlite has to be compiled with foreign key support
2. You still have to turn it on for each connection with PRAGMA
3. You have to define the foreign key as constraint when you create the table

Ad 1) Quoted from here:

If the command "PRAGMA foreign_keys" returns no data instead of a single row containing "0" or "1", then the version of SQLite you are using does not support foreign keys (either because it is older than 3.6.19 or because it was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).

What is your result for PRAGMA foreign_keys;?

Update: from your comment I see you are using 3.6.14.2, this means your version is not supporting foreign key constraints! So you have to update SQLite, if this is possible!

Ad 2) Your first code snippet executes the PRAGMA as statement, I don't think this will work. The third snipped didn't work based on your comment: the SQLite driver interprets the whole string as the location of the database, instead of taking the "foreign keys=true" part as connection settings". So only the second snippet will work.

Ad 3) Did you create the table with foreign key support? Quoted from here:

CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);
CREATE TABLE track(
  trackid     INTEGER, 
  trackname   TEXT, 
  trackartist INTEGER,
  FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

SQLite: Why is foreign key constraint not working here?

You need to first enable foreign key enforcement:

PRAGMA foreign_keys=on;

---

Related Topics