Foreign Keys and "Back Relationships" in 3.5.2

Classic Servoy
1

Using the create button on the relationship window creates relationships from fk’s defined in my MySQL DB (with naming convention fk_…), which is great. But, since 3.5.2, it also appears to create back relationships?

Is this documented somewhere? If so, I would be very interested in understanding better the logic behind the back relationships, b/c even though the fk’s are properly created on the reference table, the back relationships are a bit confusing to me and fro what I can make of them not functional in all cases.

For example…

I have a table named shipment with 10 separate fk’s pointing back to address id on a table named “address” (any one shipment has 10 different addresses). All 10 fk’s were created properly, and individually on the address table, but oddly were combined into a single back reference on the shipment table with a multiple column join. Combining the columns into a single relationship is using “And” not “Or” correct…so how will this work? I don’t need a relationship pointing back to address where all addresses id’s match, that is not possible.

Any help would be appreciated.

2

The idea behind the back-relation is that in Servoy you would use relations in two ways.
For example, if you have a FK in the orderline table to the orders table 2 relations are generated, 1 from orders to orderline and 1 from orderline to orders.
You would need both relations to navigate from order to line or from line to order.

Unfortunately, when there are multiple FKs between 2 tables (as in your shipment/addresses example) a name clash in the created relations prevents more than one back-relation to be created.

Rob

3

Hi Rob, and thanks for your reply.

I appreciate you acknowledging the issue but could you also provide me with a little insight as to what the roadmap is for this? Meaning is this officially an “bug” that Servoy plans to address (in which case I may wait for a fix) or I should simply understand it, work around it and move on?

4

Should we file a bug report, or is this something servoy is already working on to resolve?

5

Hello,

In the case of the order/orderline example, the back-relation is useful, because in servoy it is used to go from an ordeline to an order.

In the case of the shipment/address example, the back-relation will be rarely used. It will not be often that you will need to go from an address to find the shipment/billing/etc record that used the address.
Adding all back-relations would also affect the order/details case because the name of the relation would have to be extended with column names or sequence numbers.

In the rare case that you will use those relations, you can still create them by hand and give them sensible names.

Rob

6

What I am trying to understand from Servoy is whether or not the back relations are going to be modified to support the scenario described. If they are, I am happy to wait for whatever release it is that is going to address this issues. Otherwise, I will create the back relations myself.

Please advise. And thank you for your replies.

7

I’m not sure it was entirely clear what we are experiencing here, let me try again (I work for jbader)…

(I will abbreviate the amount of fk’s for everyone’s sanity)
In a table called ‘shipment’ we have 3 fk’s back to the ‘address’ table…

CREATE TABLE `shipment` (
  `shipment_id` int(10) unsigned NOT NULL auto_increment,
  `id_address_customer_bill_to` int(10) unsigned default NULL,
  `id_address_customer_ship_to` int(10) unsigned default NULL,
  `id_address_customer_pay_to` int(10) unsigned default NULL
  PRIMARY KEY  (`shipment_id`),
  CONSTRAINT `fk_shipment_to_address_customer_bill_to` FOREIGN KEY (`id_address_customer_bill_to`) REFERENCES `address` (`address_id`),
  CONSTRAINT `fk_shipment_to_address_customer_pay_to` FOREIGN KEY (`id_address_customer_pay_to`) REFERENCES `address` (`address_id`),
  CONSTRAINT `fk_shipment_to_address_customer_ship_to` FOREIGN KEY (`id_address_customer_ship_to`) REFERENCES `address` (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 290816 kB; (`id_address_customer_pay_to`) REFER';

Now when we hit the ‘create’ button in the relations tool in servoy there is a single relation created under the shipment table called ‘shipment_to_address’ where inside of it has all three of these fk’s AND’ed (inner join) together …

id_address_customer_bill_to = address_id
id_address_customer_ship_to = address_id
id_address_customer_pay_to = address_id

Now over in the ‘address’ table servoy has created three relations called…
fk_shipment_to_address_customer_bill_to
fk_shipment_to_address_customer_pay_to
fk_shipment_to_address_customer_ship_to

The problem is we want to get the values from the ‘address’ table while on the shipment form, however that never works since servoy has mixed all the possible addresses in the ‘shipment’ table into one relation. The relations that where created on the ‘address’ table (with fk_) are what I want on the shipment table, it seems that servoy created them backwards.

8

Any update as to if this is expected behavior as I described, or a bug?

9

Hi,

I have made a fix for this in next 3.5-release of Servoy.
When there are multiple foreign key constraints between the same table, the conditions will no longer be combined into 1 relation (which does not make sense indeed), but separate relations will be created, called taba_to_tabb_1,taba_to_tabb_2, etc.

Rob