I am implementing a database in my Android application and it is giving me more problems than I thought at first.
My BD organizes the daily work of the students, noting the work they have to deliver, the subjects, the teachers, the studies, the events (excursions), schedules, etc.
It turns out that the tables Asignatura
, Evento
and Deber
are linked to the table Horario
with one-to-many relationships , leaving the Horario
foreign keys to their respective tables in the table. Just as it appears in the following image:
These foreign keys must be null because a schedule can only belong to a single Evento
or to a single Asignatura
or to a singleDeber
, but Android's SQLite does not allow entering foreign keys with a data that does not exist in the related table or a null data (it is impossible because is of type int
).
I explain an example:
The data
Asignatura_id
,Evento_id
andDeber_id
are of typeint
so I can't insert a valuenull
into the field that has a binding (FK).If I insert the
Horario(Fecha, Hora_Inicio, Hora_Fin, Asignatura_id, Evento_id, Deber_id)
following data, I putEvento_id
andDeber_id
with values-1
to say that they are not related to anything.horario.add(new Horario("2016-03-15", "16:00:00", "19:00:00", 2, -1, -1));
The result is the following error in the
logcat
:Error inserting Deber_id=-1 Evento_id=-1 Hora_Inicio=16:00:00 Asignatura_id=2 Fecha=2016-03-15 Hora_Fin=19:00:00 android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787)
This error is given because the value does not exist in the and
-1
tables .Evento
Deber
With all that said, my question is: How can I fix this? Can I put some value that does the same function as null?
I have thought of several solutions:
Create an invalid record in each of the three tables and when you want to put a null value in the table
Horario
, reference it. For example: create a first record with all possible data set to null, so the first record of the three tables (with_id
= 1) would be a wildcard.Create a hierarchy starting from
Horario
, that is, create the following tables:- Schedule: _id, Date, Start_Time and End_Time (not null).
- TimeEvent: _id, Event_id (not null, FK to Event._id).
- ScheduleSubject: _id, Subject_id (not null, FK to Subject._id).
- DutySchedule: _id, Duty_id (not null, FK to Duty._id).
I would like to know what you think about it or if you find another solution that is easier to implement.
My main issue is time as I need to get the DB up and running by next week so I need something quick to set up and get it working.
I am open to any solution and any help. I can reward her.
Thanks in advance.
In the end the solution was to create a hierarchy. I have answered myself in the same question. Even so thanks.