Postgresql MATCH PARTIAL work around?

Question

I'm trying to work around Postgresql 8.4's lack of MATCH PARTIAL. I have the following schema:

[vehicles]
lot_id | vin | source | year | make | model ...
  primary key ( lot_id, vin, source )

[pictures]
picture_id | lot_id | vin | url | sha1 ...
  primary key ( picture_id )

Now, what I want is a compound FOREIGN KEY that REFERENCES the vehicles table, such that it requires a lot_id and vin to exist in the vehicles table or the integrity constraint on the pictures table fails. The problem is this functionality is only available in MATCH PARTIAL which isn't implemented. Is there any other way to easily get this effect? Prior to the current schema iteration my vehicles table would have columns for each source automated_make override_make vin_decode_make this was getting to be a mess. But, it appears without MATCH PARTIAL I'll have to make a bigger change than I originally intended.

I think I'll have to keep two compound indexes to achieve this.

[index]
lot_id, vin
  primary key ( lot_id vin )

Maybe renaming [vehicles] to [sources] in the process; and, then forcing both [vehicles] and [pictures] to MATCH FULL against these this excessive table's PRIMARY KEY.

Answer 1

You're having this problem because you've got a bad data model.

A vehicle should be uniquely identified by vin (Vehicle Identification Number). The identity of the vehicle doesn't change based on what lot it is in. And pictures of it are unlikely to change based on the lot it is in (unless you care about, e.g., "picture of this Audi in Lot 4").

So pictures should foreign key on vehicle(vin), not vehicle and lot.

Now, a vehicle can be in a lot, and maybe for your model, it must be in a lot. So add a table of lots, and give vehicle a FK to it.

Bit the bullet and make the model change, rather than wasting time trying to accommodate the poor model.

Answer 2

I agree with your idea that vehicles are not defined by just vin but the combination of vin and lot since this is not physical vehicles. Personally I would remove source from the primary key, and break out source specific data into an own table. So we have:

[vehicles]
lot_id | vin
  primary key ( lot_id, vin )

[vehicle_data]
lot_id | vin | source | year | make | model ...
  primary key ( lot_id, vin, source )
  foreign key ( lot_id, vin ) references vehicles

[pictures]
picture_id | lot_id | vin | url | sha1 ...
  primary key ( picture_id )
  foreign key ( lot_id, vin ) references vehicles

Even if you manually override parts of the description it is still the same vehicle.

Answer 3

This is a bad model. You either want to match or you don't. The whole partial idea specifically exists to deal with bad models. If you really have no other choice, write a trigger.