I am in a situation in which I have a base table with some fields and a discriminator field which could have one out of a specific set of values.
From that base table, I have derived many views, one for each possible discriminator value, but such views do not include the discriminator column themselves.
The problem is, when I try to INSERT INTO one of the view, the insert succeedes, but the discriminator field in the base table is NULL.
Isn't there a way for me to insert in the view while having the discriminator value correctly set? Or do I need necessarily to insert in the base table instead?
Here is an example of what I mean:
create table base_table ( a int unsigned auto_increment primary key, b char(10) default null, c char(10) default null, x tinyint unsigned default null ); create or replace view base_view_1 as select a, b, c from base_table where x = 1; create or replace view base_view_2 as select a, b, c from base_table where x = 2; [ ... and so on ... ] insert into base_view_1 (a, b, c) VALUES (null, 'xxx', 'xxx'); select * from base_view_1; -- no result select * from base_table; -- one row like (1, 'xxx', 'xxx', null)
See that, in the example, the row from base_table as
null as the value for
x? Should mysql be able to populate that field automatically based on view definition?
What I would like to achive is that, when I insert on the view
base_view_1, since it is defined for
x = 1, mysql sets automatically the correct value in
base_table, and the same when inserting in
x should be
2, and so on, for example:
insert into base_view_1 (b, c) values ('xxx', 'xxx'); // should insert into base_table the values ('xxx', 'xxx', 1) insert into base_view_2 (b, c) values ('xxx', 'xxx'); // should insert into base_table the values ('xxx', 'xxx', 2) // and so on for each distinct discriminator value
Now, is mysql capable of doing this automatically for me, or do I need to resort inserting directly on base_table?