2

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?

5
  • Are you getting any error messages doing an insert into the view ? If so, does the error involve x ? Commented May 29, 2014 at 13:58
  • There are no error, what I'm saying here is just that x is null while I'd expect mysql to recognize that, in my example, it should be 1, 'cause base_table_1 is a view defined on the condition x = 1, so it should automatically set it to 1. Can mysql do such a thing? Commented May 29, 2014 at 14:03
  • Update my answer. Please look ... Commented May 29, 2014 at 14:07
  • Why do you allow x to be null in the base_table? Commented Jan 10, 2016 at 9:28
  • That's just how the table is, even if it was defined as not null, it would just change the error Commented Jan 10, 2016 at 11:23

1 Answer 1

1

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 !!!

3
  • I do have an auto-increment, that's the primary key, I just wrote the wrong table definition. The problem here is not a being null, for it is not, but x is null while I would expect mysql to place the proper discriminator in it. Commented May 29, 2014 at 13:50
  • Please note that the two insert queries in my answer does not need a placeholder for x since its default is NULL. Commented May 29, 2014 at 13:57
  • Still not what I am trying to achive, I'll update my question to show better what I would like. Commented May 29, 2014 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.