0

Considering a tables schema as following below:

create schema simulado;
use simulado;

create table mo(
id integer Primary Key auto_increment,
groupTypologyId int,
typologyId int,
contractId varchar(45),
tradeDate datetime,
index (id)
);

create table eq(
id int,
typologyId int,
typologyName varchar(20),
delta decimal,
index(typologyId),
constraint fk_ideq foreign key (id) references mo (id)
);

create table cm(
id int,
typologyId int,
typologyName varchar(20),
shortleg decimal,
index(typologyId),
constraint fk_idcm foreign key (id) references mo (id)
);

create table ir(
id int,
typologyId int,
typologyName varchar(20),
isin varchar(20),
index(typologyId),
constraint fk_idir foreign key (id) references mo (id)
);

create table fx(
id int,
typologyId int,
typologyName varchar(20),
dma bit,
index(typologyId),
constraint fk_idfx foreign key (id) references mo (id)
);

alter table mo 
add constraint fk_eq foreign key (typologyId) references eq(TypologyId),
add constraint fk_fx foreign key (typologyId) references fx(TypologyId),
add constraint fk_ir foreign key (typologyId) references ir(TypologyId),
add constraint fk_cm foreign key (typologyId) references cm(TypologyId);

I need to insert the data in such a way that, from the values of the groupTypologyId column by mo, the data is taken to columns referring to the groupTypologyId. For example, groupTypologyId = 1 refers to table eq, and groupTypologyId = 2 refers to table fx.

Mo Table
id | groupTypologyId | typologyId | contractId | tradeDate
1  |      1          |      1     | xxxxxxxxxx | 03/04/2020
2  |      2          |      5     | yyyyyyyyyy | 03/02/2020

Eq Table 
id | TypologyId | TypologyName | Delta
1  |     1      |     Eq A     | 0.5

Fx Table
id | TypologyId | TypologyName | dma
2  |     5      |     Fx A     |  0

So, I would like to create a command that analyzes the value of groupTypolyId in mo, and from that analysis direct TypologyId to the table referring to its typology and then perform another insert with the specific columns of each table. Is it possible to do this in MySQL? I am trying, unsuccessfully, with the command below, which tries to get the Id inserted by hand, that even if it was successful, this is not the way I want to do it. 

use simulado;
insert into mo values (1,1,1,'xxxx', now());
select Last_Insert_Id() into @gp;
insert into eq values(@gp, 1, 'eq a', 0.5);
Costa.Gustavo
  • 849
  • 10
  • 21
  • 1
    Why do you have a cyclic foreign key reference between the `mo` table and the other tables? You have one foreign key from the sub classes to the `mo` table via the `id` table and then four other foreign keys, which are for the same column in four different tables for the `typologyId` column. The fact that you have done this with an `ALTER TABLE` statement looks like this is not what you want. What are you trying to save in the database and why do you think you need cyclic foreign key references? And what are the meanings of these table names (maybe their full name makes more sense). – Progman Apr 04 '20 at 21:20
  • The foreign key (groupTypolgyId) in hand represents a column. My idea is that each of the subclasses (cm, eq, fx ir) receive values from 1 to 4, and if in mo, groupTypologyId receives for example 1, the operation should be directed to the cm table, and this operation will have the data specific to the table. It is like reading from a .csv file the values of your columns plus the specific values of a table and from groupTypologyId the operations would be directed to the column referring to that value. – Costa.Gustavo Apr 05 '20 at 01:54
  • The names of the tables have no meaning, but thinking about OO is as if it were the parent class and all the others inherited their attributes. – Costa.Gustavo Apr 05 '20 at 01:54
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – Progman Apr 05 '20 at 08:21
  • Also relevant: https://dba.stackexchange.com/questions/36573/how-to-model-inheritance-of-two-tables-mysql. – Progman Apr 05 '20 at 08:22

0 Answers0