Integrity constraints with type inheritance

[Yan Pujante]

I am having the following issue that I am not sure how to solve:

I have a base type:

create type base_typ as object (
id number,
f1 number) not final;
/

and a derived one

create type d1_typ under base_typ (
f2 number);
/

Now I create a table of the base type

create table my_table of base_typ;

From the oracle documentation, a type doesn't have integrity
constraint but you can add some on the table, so for example I can
say:

alter table my_table modify f1 constraint my_table_n1 not null;

Using substitutabilit y, it is possible to do
insert into my_table values (d1_typ(1, 2, 3));

How can I put a constraint on the table saying for example that f2 >
f1 ? I cannot define it at the type level and I don't know how to
define it at the table level since it is a table of 'base_typ'.

I know that if I create a table of type d1_typ then I can simply
define the constraint, but that is not the point... there may be
d2_typ, d3_typ... in the future and that would be impracticle to
create that many tables (especially if there is another table that
references my_table).

Thanks for any help.

[Daniel Roy]

Re: Integrity constraints with type inheritance

By creating your table my_table as of type base_typ, you stop yourself
from being able to apply constraints referring to attributes of d1_typ
only. According to Oracle (and any other OO languages), your variable
"f2" does not even exist in the context of my_table. I therefore don't
think that what you're trying to achieve is possible. I know it's not
practical, but you'll have to define your tables of the type at the
bottom of the hierarchy tree if you want to define constraints
accessing variables only at the bottom of the tree. If you constantly
expand on your hierarchical tree, go back to the drawing board and
re-think your design approach.

Daniel