What is causing Foreign Key Mismatch error?

by

I’m not familiar with SQLite but a little Google’ing turned up this. The documentation says

If the database schema contains
foreign key errors that require
looking at more than one table
definition to identify, then those
errors are not detected when the
tables are created. Instead, such
errors prevent the application from
preparing SQL statements that modify
the content of the child or parent
tables in ways that use the foreign
keys. Errors reported when content is
changed are “DML errors” and errors
reported when the schema is changed
are “DDL errors”. So, in other words,
misconfigured foreign key constraints
that require looking at both the child
and parent are DML errors. The English
language error message for foreign key
DML errors is usually “foreign key
mismatch” but can also be “no such
table” if the parent table does not
exist. Foreign key DML errors are may
be
reported if:

  • The parent table does not exist, or
  • The parent key columns named in the foreign key constraint do not exist,
    or
  • The parent key columns named in the foreign key constraint are not the
    primary key of the parent table and
    are not subject to a unique constraint
    using collating sequence specified in
    the CREATE TABLE, or
  • The child table references the primary key of the parent without
    specifying the primary key columns and
    the number of primary key columns in
    the parent do not match the number of
    child key columns.

I suspect you might be running into #3 in that list.

Also, while other DBs might support using a non-unique index as a foreign key reference, (see answers here), it’s a bad design choice in my opinion. I would restructure so that either

  1. Reading.PatientId references Event.PatientId so that the complete composite key from Event is referenced by Reading or,
  2. Add an EventId auto-increment, primary key to the Event table and use that as the foreign key in the Reading table (so that you only have EventId and Value under Reading and you can get the PatientId, DateTime, EventTypeCode out of Event).

I’d suggest #2 so that you can avoid the redundancy of PatientId, DateTime and EventTypeCode in both Event and Reading.

More Related Contents:

  1. How do I add a foreign key to an existing SQLite table?
  2. MS SQL “ON DELETE CASCADE” multiple foreign keys pointing to the same table?
  3. How to select rows with no matching entry in another table?
  4. What are valid table names in SQLite?
  5. Deleting duplicate rows from sqlite database
  6. Delete column from SQLite table
  7. sqlite database default time value ‘now’
  8. How do I insert datetime value into a SQLite database?
  9. How can I find which tables reference a given table in Oracle SQL Developer?
  10. How can I INSERT data into two tables simultaneously in SQL Server?
  11. basic recursive query on sqlite3?
  12. SQLite and custom order by
  13. SQLite auto-increment non-primary key field
  14. How to do IF NOT EXISTS in SQLite
  15. Core Data Performance with Single Parent Entity
  16. Match only entire words with LIKE?
  17. sql unique constraint on a 2 columns combination
  18. How to split comma-separated values?
  19. Display names of all constraints for a table in Oracle SQL
  20. sqlite select with condition on date
  21. How to compare sqlite TIMESTAMP values
  22. SQLite Order By Date1530019888000
  23. SQL – Inserting a row and returning primary key
  24. Why are foreign keys more used in theory than in practice?
  25. SQL error: misuse of aggregate
  26. SQLite query, ‘LIKE’
  27. UPSERT *not* INSERT or REPLACE
  28. Drop column from SQLite table
  29. converting int to real in sqlite
  30. What is difference between foreign key and reference key?

Leave a Comment