Mysql – Insert into a view derived from a table with a discriminator field

MySQLmysql-5.1view

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 base_view_2 where 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?

Best Answer

You should do your INSERTs into the base table only

That way you can INSERT queries like these

insert into base_table (a, b, c, x) VALUES (null, 'xxx', 'xxx',1);
insert into base_table (b, c, x) VALUES ('xxx', 'xxx',2);

If you do INSERT queries like these

insert into base_table (a, b, c) VALUES (null, 'xxx', 'xxx');
insert into base_table (b, c) VALUES ('xxx', 'xxx');

then you should assign a default value of 1 for x in the table definition

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 1
);

Otherwise, you should only INSERT into base_table since there exists no mechanism for dynamically switching the default value of x within the definition of a view. The x column has no ability to be selected in the view (because you left it out of the SELECT clause). Consequently, it would definitely have no ability to be updated or assigned in the view.

Give it a Try !!!

Related Question