Foreign key constraint fails via Eloquent yet works in the database

I'm trying to run an insert query on a SQLite database which results in the following QueryException:

Illuminate\Database\QueryException

SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed (Connection: sqlite, SQL: insert into "stock_price_movement" ("stock_symbol_id", "price", "updated_at", "created_at") values (1, 2288300, 2024-11-27 07:00:51, 2024-11-27 07:00:51))

Oddly, running the query directly (see below) using sqlite3 sees it succeed. Any suggestions as to what might be going wrong?

insert into "stock_price_movement" ("stock_symbol_id", "price", "updated_at", "created_at") values (1, 2288300, '2024-11-27 07:00:51', '2024-11-27 07:00:51');

josepostiga

1 year ago

Best Answer

Level 10

SQLite documentation states that foreign key constrains check is disabled by default (source: https://www.sqlite.org/foreignkeys.html; second paragraph of the "Overview" section).

Furthermore, if we check the framework's database configuration file (config/database.php), under the sqlite connection options, the default option to check for FK constrains is activated by default, unless stated otherwise via its environment variable.

That's why you get different behaviors when running the query via the framework vs directly into sqlite.

1 like

settermjd

1 year ago

Level 1

Thank you for the feedback.

Please or to participate in this conversation.