I have a units table and a reports table. A unit has many reports. So reports table has a foreign key unit_id which corresponds to the id of the unit. Hence, there is a foreign key constraint in reports table.
I began to realize that sometimes reports need to be created even if the unit doesn't exist for them yet. So when I tried to create a report without a unit, I obviously got this error:
ERROR: insert or update on table "reports" violates foreign key constraint "reports_unit_id_fkey" Detail: Key (unit_id)=(7295) is not present in table "units".
My question is if I remove this foreign key constraint and enable reports to be created without a unit, will this affect performance when I try to get all reports associated with a unit?
SELECT * FROM reports WHERE unit_id=7295;
The only other option I have is to insert the reports into some orphans table, but then I would have to copy those orphans over when the unit is created, which is additional work.