Re: Database design confusing pg_restore, and misc pg_restore issues

On 08/05/2010 10:33 PM, Tom Lane wrote:
> Since you say that --disable-triggers doesn't help, I guess that you're
> applying that function not in a trigger but in a CHECK constraint?
> That's pretty horrid in itself: CHECK is *not* meant to enforce anything
> except local properties of the newly inserted/updated row itself.
> Aside from the ordering problems that you've already run into some of,
> consider what happens when the referenced row gets deleted. (Hint:
> nothing.)

Luckily, they never get deleted :)

Okay, well, I guess one solution is to replace the checks with triggers
on all tables involved. That's not pretty, and really doesn't express
the concept of a constraint very clearly, but I guess it would work.

> Sure you can't find a way to unify reginfo1/reginfo2 into one table?
> If you have some side information that doesn't fit conveniently into
> that table, maybe making an auxiliary table that's foreign-keyed to
> the master reginfo table would help. But you really need a structure
> that allows you to declare the order_item table with a regular foreign
> key for reginfo.

So, your first suggestion would look like this:

> reginfo(order_id, product_id, reginfo1_columns, reginfo2_columns, FOREIGN KEY(order_id, product_id) REFERENCES order_items)

For the sake of illustration, let's say that order_item's foreign key to
this table is NOT NULL.

So, if the product in question uses regtype1, then the reginfo2 columns
are NULL, and vice versa. If the product doesn't use any registration,
then both the reginfo1 and reginfo2 columns are NULL.

The problem is, how do I express that requirement in a constraint? And
without updating the schema every time I add a new product?

Your second suggestion would look like this:

> reginfo(order_id, product_id, FOREIGN KEY(order_id, product_id) REFERENCES order_items)
>
> reginfo1(order_id, product_id, reginfo1_columns, FOREIGN KEY(order_id, product_id) REFERENCES reginfo)
>
> reginfo2(order_id, product_id, reginfo2_columns, FOREIGN KEY(order_id, product_id) REFERENCES reginfo)

Well, at that point, the reginfo table is redundant, and the reginfo1
and reginfo2 tables may as well reference order_items directly, which is
exactly what I have, minus my problematic constraint.

My assumption is that most people would simply give up and assume that
this constraint is too difficult to express in SQL, and just rely on the
business logic never being wrong. I was hoping that wasn't the case :)

Thanks,

-Rick-