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
>>>
>>
