What is causing Foreign Key Mismatch error?
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
Reading.PatientId
referencesEvent.PatientId
so that the complete composite key fromEvent
is referenced byReading
or,- Add an
EventId
auto-increment, primary key to theEvent
table and use that as the foreign key in theReading
table (so that you only haveEventId
andValue
underReading
and you can get thePatientId, DateTime, EventTypeCode
out ofEvent
).
I'd suggest #2 so that you can avoid the redundancy of PatientId, DateTime
and EventTypeCode
in both Event
and Reading
.
What causes a foreign key mismatch error in sqlite database
From Required and Suggested Database Indexes:
Usually, the parent key of a foreign key constraint is the primary key
of the parent table. If they are not the primary key, then the parent
key columns must be collectively subject to a UNIQUE constraint or
have a UNIQUE index.
What you are doing wrong in your code is in the CREATE
statement of terms_table
:
FOREIGN KEY("student_id") REFERENCES "year_table"("student_id")
where you define the column student_id
to reference the column student_id
in year_table
for which there is no UNIQUE
constraint or index.
The column student_id
in year_table
just references the column student_id
of student_table
.
What you can do is define the column student_id
of terms_table
to reference the column student_id
in student_table
, which makes sense:
FOREIGN KEY("student_id") REFERENCES "student_table"("student_id")
See the demo.
Foreign key mismatch when insert (SQLite)
You have:
FOREIGN KEY("date") REFERENCES "performance"("date"),
However, the primary key on performance
has THREE parts:
PRIMARY KEY("title", "date", "theaterID"),
You need to reference all three -- in the correct order -- in the foreign key declaration:
FOREIGN KEY("date") REFERENCES "performance"("title", "date", "theaterID"),
However, "title"
is not in the table, so you have to add that.
OR, just add an auto-incrementing primary key to "performance"
and use that for the reference.
Also, drop the double quotes. They just make SQL harder to write and read. And answers harder to write.
What's the reason why I'm having foreign key mismatch error on Room?
I think the problem might be that you annotate your "working entity" as an FTS-indexed entity which makes the primary key no longer usable as a foreign key (the documentation is a little unclear here).
You could drop the @Fts3
on your Envelope class and create an extra FTS-entity which maps to your original entity using @Fts4
(which lets you specify the content entity).
@Fts4(contentEntity = Envelope.class)
@Entity(tableName = "envelopes_fts")
public class EnvelopeFts {
// The fields you want to index with getters and setters
}
At least I could only get my foreign keys to work again after I split my source entity and the corresponding FTS-entity in two separate Room-entities.
Sqlite foreign key mismatch?
From SQLite Foreign Key Support/3. Required and Suggested Database Indexes:
Usually, the parent key of a foreign key constraint is the primary key of the parent table.
If they are not the primary key, then the parent key columns must be collectively
subject to a UNIQUE constraint or have a UNIQUE index.
With this:
CREATE TABLE user_disease (
...........................
med_id REFERENCES dis_med(medication_id),
...........................
);
the column med_id
of user_disease
references the column medication_id
of dis_med
, which is not the PRIMARY KEY
of dis_med
and there is no UNIQUE
constraint for it. It just references med_id
of medication
.
Why do you need the column med_id
in user_disease
?
You have dis_id
referencing disease
, which may also be used to retrieve from dis_med
(all) the row(s) from dis_med
for that disease.
What is causing this sqlite foreign key mismatch?
The documentation says:
Usually, the parent key of a foreign key constraint is the primary key of the parent table. If they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index.
In the pipelines
table, neither the name
nor the owner
columns are, by themselves, unique.
I guess you actually want to have a two-column foreign key in the tasks
table:
FOREIGN KEY(pipeline, owner) REFERENCES pipelines(name, owner)
Why do I get foreign key mismatch?
The definition of b
is:
CREATE TABLE b (
a_id INT NOT NULL,
id INT NOT NULL,
**PRIMARY KEY (a_id, id),**
FOREIGN KEY (a_id) REFERENCES a(id)
);
You have defined a composite primary key. That is, the primary key has more than one column in it. Any reference needs to use all the keys that are defined. So you need an a_id
for the reference:
CREATE TABLE c (
b_id INT NOT NULL,
id INT NOT NULL,
a_id INT,
PRIMARY KEY (b_id, id),
FOREIGN KEY (a_id, b_id) REFERENCES b(a_id, id)
);
This is one of the reasons why I find composite primary keys to be cumbersome. You can define a synthetic primary key for any table -- basically, an auto-incremented column. This is then suitable for a foreign key reference.
Related Topics
Ad Hoc Queries VS Stored Procedures VS Dynamic SQL
How to See All the Tables in an Hsqldb Database
Oracle 11G - Check Constraint with Regex
Sql: Order by Using a Substring Within a Specific Column... Possible
How to Create a Pivot Query in SQL Server Without Aggregate Function
Find the Real Column Name of an Alias Used in a View
How to Split String by Character into Separate Columns in SQL Server
Select (Retrieve) All Records from Multiple Schemas Using Postgres
Stored Procedure That Exports Data into CSV Files Only Exports to One File
Why Does Varchar Need Length Specification
Split String and Take Last Element
Sql: How to Get the Value of an Attribute in Xml Datatype
T-Sql: How to Use Parameters in Dynamic SQL
SQL Error "Ora-01722: Invalid Number"
SQL Keep Getting Error with on Update Cascade