I have a Table Appointment
The Appointment table has the following feilds:-
- Patient_Id (Foreign Key of Patient Table)/Can be Null as well
- Blocked Flag (If true, then we can block the time slot without a valid patient id).
- Time Slot
I have another table named Patient, which is linked with the Appointment table thru the Patient_Id column:-
Now, please note that in the above scenarios, I can have rows in the Appointment table, where there may not be any patient_id, so that row is not linked with the patient table.
The issue is that while fetching rows from the Appointment Table with the parameter “related=patient_by_patient_id”, the query fails. This only happens if even one of the rows returned in the query has no patient_id.
As per my understanding, this should not return and error. If we have a row then it should return me the related data, otherwise ignore.
The constraint is as below:-
ALTER TABLE appointment
ADD CONSTRAINT patient_appointment_fk FOREIGN KEY (patient_id)
REFERENCES patient (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION;
Please let me know if you consider it a bug?
I have a work-around to insert a dummy row in the patient table with an patient_id=0, but I am more keen on knowing what is the expected behavior that you folks think.