Rick Yorgason <rick@xxxxxxxxxxxxxxxx> writes: > In other words, (order_id, product_id) of order_item is a foreign key to > either reginfo1, reginfo2, or nothing, depending on which product it is. I think you'll find that few people regard that as good database design. > The works really well, until I try to use pg_dump/pg_restore, because it > attempts to restore order_items before the reginfo tables. To get it to > work properly, I need to load the schema, disable the check, load the > data, then re-enable the check. Well, you can hardly expect pg_dump to intuit that there's a dependency there; it understands nothing about the behavior of that SQL function. 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.) 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. Foreign keys are not something you can cobble together from spare parts --- a correct, robust implementation requires magic that is just not available at the user level in SQL. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
