Not sure if 'relinquish' is the right word since the foreign constraint is
going to be enforced if you enter a value for the PO. (May be my statement
was a little confusing).
Please see the example:
Create Table t1(a int primary key)
Create table t2 (b int primary key, ba null references t1(a))
I just wanted to make sure of this point.
Sundar
=========================================================
On Friday, April 20, 2012 6:03:47 PM UTC+5:30, Cliff wrote:
>
> 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
>>>>>
>>>>
