"Simon Riggs" <[EMAIL PROTECTED]> writes: >> If we don't have multi-table indexes how do we enforce a primary key >> against a partitioned set? What about non primary keys that are just >> UNIQUE? What about check constraints that aren't apart of the exclusion? > > I can come up with arbitrary examples that require them, but I've not > seen one that makes sense in a real business app. Calling columns a, b > and c disguises the validity of the example, IMHO.
Usually it comes with a situation where you want to do something like "partition invoices by invoice_date" while simultaneously "use invoice_num" as the primary key". Normally the invoices numbers will be incrementing chronologically but there's no constraint or any mechanism to enforce that or to enforce that an old invoice number from an old partition isn't reused. In practice I think this isn't really a serious problem though. The old partitions are going to be read-only so you can just check that the invoice number doesn't already exist without worrying about race conditions. And in most cases it's being sequence-generated or something equally reliable so the constraints are really just there as a backstop; you're not depending on them for correctness. At some level not having them is actually a nice thing for DBAs. It gives them an excuse for not having the constraint that will only cause them maintenance headaches down the road. But it's dangerous to go too far down that road. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
