On Thu, Mar 14, 2019 at 1:40 PM Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > Once I was finished, fixed bugs and tested it, I realized that that was > a stupid thing to have done -- because THOSE ARE DIFFERENT CONSTRAINTS.
This made me laugh. > When you say "fk (a) references pk1" you're saying that all the values > in fk(a) must appear in pk1. OTOH when you say "fk references pk" you > mean that the values might appear anywhere in pk, not just pk1. Have a > look at the triggers in pg_trigger that appear when you do "references > pk1" vs. when you do "references pk". The constraint itself might > appear identical, but the former adds check triggers that are not > present for the latter. It's probably not uncommon to have FKs between compatibly-partitioned tables, though, and in that case they are really equivalent. For example, if you have an orders table and an order_lines table and the latter has an FK pointing at the former, and both are partitioned on the order number, then it must be that every order_line references an order in the matching partition. Even if it's not practical to use the FK itself, it's a good excuse for skipping any validation scan you otherwise might have performed. But there are other cases in which that logic doesn't hold. I can't quite wrap my brain around the exact criteria at the moment. I think it's something like: the partitioning columns of the referring table are a prefix of the foreign key columns, and the opfamilies match, and likewise for the referenced table. And the partition bounds match up well enough. And maybe some other things. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
