Postgresql MATCH PARTIAL 可以解决吗？
我正在尝试解决 Postgresql 8.4 缺少 MATCH PARTIAL 的问题。我有以下架构：
[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 )
现在，我想要的是一个复合 FOREIGN KEY ，它 REFERENCES vehicles 表，这样它就需要一个 lot_id 和 vin 存在于 vehicles 表中，或者 pictures 表上的完整性约束失败。问题是此功能仅在未实现的 MATCH PARTIAL 中可用。有没有其他方法可以轻松达到这种效果？在当前模式迭代之前，我的车辆表将包含每个源 automated_make override_make vin_decode_make 的列，这变得一团糟。但是，它似乎没有 MATCH PARTIAL 我必须做出比最初预期更大的更改。
我想我必须保留两个复合索引才能实现这一点。
[index]
lot_id, vin
primary key ( lot_id vin )
也许在此过程中将 [vehicles] 重命名为 [sources]；然后，强制 [vehicles] 和 [pictures] 与此过多表的 PRIMARY KEY 进行 MATCH FULL 。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
你遇到这个问题是因为你的数据模型不好。
车辆应由vin（车辆识别号）唯一标识。车辆的身份不会根据其所在批次而改变。并且它的图片也不太可能根据其所在批次而改变（除非您关心，例如“第 4 批次中这辆奥迪的图片”） 。所以图片应该是车辆（vin）上的外键，而不是车辆和批次上的外键。
现在，一辆车可以出现很多，也许对于您的模型来说，它必须出现很多。因此，添加一个批次表，并给车辆一个 FK。
咬紧牙关并改变模型，而不是浪费时间试图适应糟糕的模型。
You're having this problem because you've got a bad data model.
A
vehicleshould be uniquely identified byvin(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.
我同意你的想法，即车辆不仅仅由 vin 定义，而是由 vin 和很多的组合定义，因为这不是物理车辆。就我个人而言，我会从主键中删除源，并将源特定数据分解到自己的表中。所以我们有：
即使您手动覆盖部分描述，它仍然是同一辆车。
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:
Even if you manually override parts of the description it is still the same vehicle.
这是一个糟糕的模型。你要么想匹配，要么不想。整个部分想法专门用于处理不良模型。如果你实在没有其他选择，那就写一个触发器。
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.