nick,
Thanks for the response.
Here's a longer explanation.
Two applications, one is purchasing, the other is production.
Purchasing has two tables, purchase_orders and product_lots. It's a
one-to-may relationship; a purchase order can have many product lots. So
we get:
db.define_table('purchase_orders', field('po_number' .....
db.define_table('product_lots', Field('purchase_order_id',
db.purchase_orders, requires=IS_IN_DB(db, 'purchase_orders.id', ...)),
Field('quantity_received', 'decimal'), Field('quantity_on_hand',
'decimal') ...
So, I have established a foreign table relationship between purchase orders
and product lots.
Once a product_lot is released to production, the production app uses the
same table to find available raw materials, and decrements the
quantity_on_hand field as it goes. The production app neither knows nor
cares about the purchase order table, so the purchase_order_id field is
irrelevant.
This is fine as long as all production material enters the plant via the
purchasing process. However, this is not the case. So, either I devise a
way to get records into the product lots table without violating the
foreign key constraint that Postgres would apply. One way would be to
define the purchase_order_id field as an integer and handle the key
relationships through code. Not attractive.
Another way would be to establish a third table for raw materials. This
would be a clone of the product_lots table but it would not have a foreign
key to purchase_orders. The purchase_order controller would then need to
create and update records in the raw materials table, while several
controllers in the production app would also create and update records in
the table as well. I don't like this solution much, either.
Can anybody suggest another approach?
On Thursday, April 19, 2012 1:03:41 PM UTC-4, nick name wrote:
>
> I don't understand what you are trying to achieve, but whatever it is, you
> are doing it wrong; your model should be:
>
> db.define_table('A', Field('name'))
>
> db.define_table('B', Field('name'), Field('id_from_table_a', 'reference
> A'))
> # alternatively:
> # db.define_table('B', Field('name'), Field('id_from_table_a', db.A))
>
> This would create the foreign key reference, and would allow nulls by
> default (which is what your condition does).
>
> But as I said, I'm not sure exactly what you are trying to achieve?
>
> On Wednesday, April 18, 2012 7:11:42 PM UTC-4, Cliff wrote:
>>
>> There are two tables, as follows:
>>
>> db.define_table('A', Field('name'))
>>
>> db.define_table('B', Field('name'), Field('id_from_table_a))
>>
>> Also there are two applications. One, called 'both_a_and_b', uses both
>> tables and uses 'table_a_id' as a foreign key in table B. The other,
>> called 'table_b_only' needs CRUD access to the information in table B, but
>> it is not able to supply a value for 'id_from_table_a.'
>>
>> I think because Postgres recognizes foreign key constraints,
>> 'table_b_only' will not be able to create records in table B.
>>
>> What Is the right solution?
>>
>> I can think of two. First, create a third table, C, for all the data
>> that 'table_b_only' needs. This table would not have the 'id_from_table_a'
>> field. The other application would need to write also to this table
>> whenever it creates a record in table B.
>>
>> A second possibility might be to define table B this way:
>> db.define_table('B',
>> Field('name') ,
>> Field('id_from_table_a', requires= IS_EMPTY_OR(IS_IN_DB(db, 'A.id',
>> ...)))
>> )
>>
>> I would be grateful for any guidance,
>> Cliff Kachinske
>>
>
