Support Ecto.Changeset.foreign_key_constraint/3 ?

[jjcarstens]

I have a relational setup that I would like to just give me an {:error, changeset} tuple back for when the foreign record doesn't exist. However, no matter what I put I always get this error:

14:02:56.563 [error] Task #PID<0.559.0> started from #PID<0.557.0> terminating
** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    *  (foreign_key_constraint)

If you would like to stop this constraint violation from raising an
exception and instead add it as an error to your changeset, please
call `foreign_key_constraint/3` on your changeset with the constraint
`:name` as an option.

The changeset defined the following constraints:

    * intervals_schedule_id_fkey (foreign_key_constraint)

...

If I'm understanding right, SQLite knows there is a constraint here, but its unnamed and so all we get back to help is an empty * (foreign_key_constraint) clue. And since there is no name to match on, Ecto can't reconcile it and things just crash. I've even tried using the name: "" option, but I think Ecto just ignores the empty string.

Is it even possible to get around this or are we limited by the abilities of SQLite here? I do understand I can use the foreign_key: :off option in the adapter to just skip validations entirely which is currently my workaround, but I would love to get the {:error, changeset} return instead.

Caveat: I am in no way a DB expert and this could be 100% user error. My migrations are simple and would love to know if Im just doing things wrong:

create table(:schedules) do
  add :name, :string
end

create table(:intervals) do
  add :schedule, references(:schedules)
end

[kevinlang]

Yeah this is a SQLite3 issue. See this issue for more details: https://github.com/elixir-sqlite/ecto_sqlite3/issues/28

We have a hack that works for some cases. I believe I tried adapting that hack for foreign keys in the above issue, but failed.

I encourage you to post a request on the SQLite3 forum asking for some sort of API from SQLite3 so we can support this! :smile: Or not even an API, just some sort of message we can use, even if it is subject to change

In the meantime, I'll try thinking about whether there may be other hacks we can do. I'll play around a bit an re-open this if I find a new approach. I'll also update the docs to make this a known limitation.

[kevinlang]

For reference, here is what we get back from SQLite3 and thus why we cannot really do anything:

sqlite> pragma foreign_keys=on;
sqlite> create table a(b integer not null primary key); 
sqlite> create table c(d references a(b));
sqlite> insert into c values(1);
Error: FOREIGN KEY constraint failed

No mention of which constraint failed. See https://sqlite.org/forum/forumpost/b17fdd0d59 for examples on other types of constraints.

[kevinlang]

I've added some documentation about this limitation that points back to this issue.

[binajmen]

Hi @jjcarstens, sorry for this 2 years+ later ping but I'm considering Turso database (SQLite-based database) and I ended up on this thread while reading the limitations of SQLite3 support with Ecto.

Should I understand that even now foreign keys are not properly supported? Did you end up with a viable alternative or disabling foreign keys was the only solution?

[jjcarstens]

My use-case had flexibility so I ended up disabling foreign keys when able and capturing and transforming the error when absolutely needed. Definitely a "made it work and moved on" situation. Hope that helps