Thank you, Sundar.
I understand. This solution means I relinquish the foreign key constraint
on the product_lots table.
It does seem simpler than using another table for raw materials.
No third alternative, I guess...
On Friday, April 20, 2012 2:37:38 AM UTC-4, Sundar wrote:
>
> Cliff
>
> By adding the 'null' to the 'purchase_order_id' in the product_lots table,
> you automatically achieve it - that is, if the field is declared as Null,
> foreign key constraint is applicable only if it is non-null and a null
> value will automatically skip validation for foreign key.
>
> Another method (not really required, I guess) is to define a 'dummy' PO
> with id as zero (or non-negative) and refer to this PO in all product lots
> which do not require any PO reference. However, this method will mean that
> you have to have an additional where clause in all selects (on the PO
> table) to exclude this specific PO.
>
> Cheers.
>
> Sundar
> ==============================================
> On Friday, April 20, 2012 3:16:24 AM UTC+5:30, Cliff wrote:
>>
>> 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
>>>>
>>>
