#24 Feature Request - smart conditional outerness of joins for dodgy fk's

[Malcolm]

I have a database with really unreliable foreign key relationships. No integrity constraints were created on the schema so there's always the chance that a foreign key will have a non-existent UID or it will be null when it shouldn't be.

A simplified version of my schema is:

table Vehicle {
    vehicle_uid INTEGER,
    rego STRING,
    fk_vin INTEGER
}

table VIN {
    vin_uid INTEGER,
    vin STRING
}

A vehicle should always have a vin, so Vehicle.fk_vin should always be non-null and be a valid FK to a VIN record. However, sometimes fk_vin is null, and sometimes it is for a vin UID that doesn't exist.

I'm writing a search screen with the following optional search criteria fields:

• rego
• vin

The search results page shows the vehicle information, including rego and vin. If the user searches on just rego, then the generated SQL should do an outer join to the VIN table, to cope with the dodgy FKs, ie (using IBM Informix syntax):

select Vehicle.*, VIN.*
from Vehicle, outer(VIN)
where vehicle.fk_vin = vin.vin_uid and vehicle.rego = ${criteria.rego};

If the user searches on vin, then the VIN table must be included as an inner join, ie:

select Vehicle.*, VIN.*
from Vehicle, VIN
where vehicle.fk_vin = vin.vin_uid and vin.vin = ${criteria.vin};

I'd really like it if DBvolution was able to do this sort automatically. The idea is to flag an FK column as "defaulting to optional", and then the following rule gets applied on top:

• if there are no criteria on any columns within the referenced table, it is outer joined;
• if there are any criteria on any columns within the referenced table, it is inner joined.