Forum OpenACS Q&A: Response to SQL: how do you do this?

You've posed an interesting problem. It's made an eensy bit more complicated in that the names of columns and tables don't really communicate what it's for.

Your original question has already been answered by Michael Bryzek -- just add the final referential constraint after BOTH tables have been created.

A couple of people have questioned why you'd ever have a circular situation like this. My own classic example of this is the notion of component->subcomponent. In the case of a car, you might have something like:

  Transmission (component)
     |
     v
  Clutch assembly--+  (component AND subcomponent)
     |             |
     v             v
  Clutch disc     Pressure plate

A person has the the option of buying an entire clutch assembly or just the clutch disc. In order to define prices (and other descriptive information, like where the parts originate, etc.) for the both components AND subcomponents while keeping the structure, you'd have to break it into at least two entities, with the circular references between them.

Back to the problem at hand, if I understand this correctly, shipments from a given origin to a given destination have a variety of prices (based on quantity) that can be assigned. One rate in the rate chart is marked as the "default" (e.g., most people send 3000 pounds of whatever at a time on that route, so we'll make that the standard). Another is what is "selected" (Jade wants to send 10,000 pounds, so we'll cut a good deal).

The problem as I see it is that the default_q attribute goes with the shipping route (foo_vf), while selected_q goes with a particular shipment along that route. If I understand this correctly, you actually need a third entity.

     create table foo_vf_quantity (
            id                       integer not null primary key,
            quantity                 number not null, 
            cost                     number,
            notes                    varchar(2000),
            vf                       integer not null,
            last_user_modifying      not null references users,
            last_modified            date not null
     );
     comment on table foo_vf_quantity is 'Pricing for available route';

     create table foo_vf (
            id                       integer primary key,
            from_v                   not null references user_groups,
            to_f                     not null references f,
            default_q                references foo_vf_quantity,
            unique (from_v, to_f)
     );
     comment on table foo_vf is 'Available routes';

     alter table foo_vf_quantity add foreign key (vf) references foo_vf(id);

     create table foo_shipment (
            shipment_id              integer not null primary key,
            route_id                 integer not null 
                                     references foo_vf( id ),
            selected_price_id        integer not null 
                                     references foo_vf_quantity( id ),
            ...
      ); 
      comment on table foo_shipment is 'Shipment for a particular route';

In this case, the selected price has moved to the foo_shipment table. One uses foo_vf to see what routes are available, foo_vf_quantity to see the pricing options for that route, and foo_shipment to actually ship something, recording the price option chosen.

It's possible I'm completely off-base in my understanding here. If so, then I apologize for muddying the waters further.