I'm trying to understand how to properly model certain kinds of
relationships. I know how I'd do it in the database, but I *seem* to be
bumping into limitations with Cayenne that can't express certain
one-way foreign key relationships.
For example, I need a table of addresses. Members can have multiple
addresses and billing accounts can have a single address. So, I've
added a member_address table to hold the links between members and
addresses. (It's not read/write when flattened, but I'll live with that
for now.)
For the billing account, I've added a foreign key column named
bill_address_id that contains an address id.
Here's where I get confused: the billing address is not optional (not
null) but when I try to generate the schema Cayenne tries to set up a
foreign key from address -> billing account because of the reverse
relationship, which is required. It can't; I see the error message
Schema Generation Error: Failed statement: ALTER TABLE public.address
ADD FOREIGN KEY (id) REFERENCES public.bill_account (bill_address_id) -
ERROR: UNIQUE constraint matching given keys for refenced table
"bill_account" not found.
If I make the bill_account's bill_address_id nullable, then I don't see
this error message. (I do see the same error message for the
member_address table; it's the same issue.)
I don't want or need a reverse relationship. It does not reflect my
needs. I want bill_address_id to be a FK to address(id), but I can't
have the reverse (address id is FK to bill_account(bill_address_id)).
Jim
-- Jim Menard, jim..o.com, http://www.io.com/~jimm/ "SPOON!" -- The Tick's battle cry "Not in the face! Not in the face!" -- Arthur's battle cry
This archive was generated by hypermail 2.0.0 : Wed Dec 17 2003 - 14:47:04 EST