Foreign Keys and "Back Relationships" in 4.1.3

Classic Servoy
1

Hi,
In relation to the topic http://www.servoy.com/forum/viewtopic.php?f=22&t=9570&p=48785
I note that the same thing happens in version 4.1.3 of Servoy with MySql 5.

Is it a bug?
Have you experienced any solution?

Thanks

2

Can you post the table-create statements and which relations you expect to be generated?

Rob

3

Sorry for the lateness:

According to the image, the idea is that a company can have two forms of payment, one for sales (sale_form_of_payment_companies_fk) And other one for purchases (form_of_payment_companies_purchase_fk).

Also can have a company who is invoiced (companies_companies_fk).

According to the code sql the relations are correct, but on having loaded them in Servoy, creates a relation for forms of payment with the indexes:

Form_of_pay_id_sale = fom_of_pay_id form_of_pay_id_purchase =form_of_pay_id
This is not correct, should create two different relations, not one with both indexes.

In case of companies does not create the relation to the same table.

Thanks and excuse my English? :)

CREATE TABLE test.form_of_payment (
                form_of_pay_id INT(10) AUTO_INCREMENT NOT NULL,
                description CHAR(35),
                PRIMARY KEY (forma_pago_id)
)ENGINE=InnoDB;

CREATE TABLE test.companies (
                company_id INT(10) AUTO_INCREMENT NOT NULL,
                name CHAR(50) NOT NULL,
                form_of_pay_id_sale INT(10) NOT NULL,
                form_of_pay_id_purchase INT(10) NOT NULL,
                billing_company_id INT(10) NOT NULL,
                PRIMARY KEY (company_id)
)ENGINE=InnoDB;

ALTER TABLE test.companies ADD CONSTRAINT sale_form_of_payment_companies_fk
FOREIGN KEY (form_of_pay_id_sale)
REFERENCES test.form_of_payment (form_of_pay_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

ALTER TABLE test.companies ADD CONSTRAINT form_of_payment_companies_purchase_fk
FOREIGN KEY (form_of_pay_id_purchase)
REFERENCES test.form_of_payment (form_of_pay_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

ALTER TABLE test.companies ADD CONSTRAINT companies_companies_fk
FOREIGN KEY (billing_company_id)
REFERENCES test.companies (company_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

Scheme.PNG
Scheme.PNG343×351 1.65 KB

4

Hi adelo.

I’ve found the same behavior on mi project.

I’ve got some tables

t100_personas

CREATE TABLE `t100_personas` (
  `id_persona` int(11) NOT NULL auto_increment,
  `id_provincia_nacimiento` int(11) default NULL,
  `id_provincia_residencia` int(11) default NULL,
  `nombre` varchar(35) default NULL,
  `apellido1` varchar(35) default NULL,
  `apellido2` varchar(35) default NULL,
  PRIMARY KEY  (`id_persona`),
  KEY `t100_personas_t100_provincias_nacimiento` (`id_provincia_nacimiento`),
  KEY `t100_personas_t100_provincias_residencia` (`id_provincia_residencia`),
  CONSTRAINT `t100_personas_t100_provincias_nacimiento` FOREIGN KEY (`id_provincia_nacimiento`) REFERENCES `t100_provincias` (`id_provincia`),
  CONSTRAINT `t100_personas_t100_provincias_residencia` FOREIGN KEY (`id_provincia_residencia`) REFERENCES `t100_provincias` (`id_provincia`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='personas'

and

t100_provincias

CREATE TABLE `t100_provincias` (
  `id_provincia` int(11) NOT NULL auto_increment,
  `nombre` varchar(35) NOT NULL,
  PRIMARY KEY  (`id_provincia`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='provincias'

in t100_personas there’re two fields pointing (FKs) the same table t100_provincias

When I import this relations into my servoy project that’s what I found:

Select relations to be created

1.- t100_personas_to_t100_provincias
2.- t100_provincias_to_t100_personas

1.-
id_provincia_nacimiento = id_provincia
id_provincia_residencia = id_provincia

2.-
id_provincia = id_provincia_nacimiento
id_provincia = id_provincia_residencia

But that’s not what I would hope.
That’s the example I think it would be the right behaviour.

Select relations to be created

1.- t100_personas_to_t100_provincias_nacimiento
2.- t100_personas_to_t100_provincias_residencia
3.- t100_provincias_nacimiento_to_t100_personas
4.- t100_provincias_residencia_to_t100_personas

1.-
id_provincia_nacimiento = id_provincia

2.-
id_provincia_residencia = id_provincia

3.-
id_provincia = id_provincia_nacimiento

4.-
id_provincia = id_provincia_residencia

Have I done anything wrong? It’s my fail? It’s a servoy fail?

I know it’s very easy to repair it by deleting one condition in the relations imported by servoy, but I’ve the doubt if there’s something I’m doing wrong.

5

This is fixed in Servoy 4.1.5

Rob