RE: Functional Indexes?
Actually no.
The pk is on id. The index is nothing more than a "backing index" to enforce selective uniqueness.
The interesting thing is that in Oracle you can have multiple row entries for NULL. Informix allows only one NULL value in the index when you have a unique index.
Because of this, you can't use a standard Informix index.
VII? Don't know enough about it, but you'd want to create an index that contained only a portion of the data to guarantee uniqueness.
This isn't breaking the relational model.
What I do suggest is that some enterprising young men at IBM who are tasked with making changes to IDS, consider creating a new type of index. Lets call it ora_idx for Oracle Compatible Index. This should assist one from taking an Oracle model to an IDS platform, simplifying the migration process.
The alternative would be to split the table in to two tables. This would also be a valid change, however now you will have to refactor code.
-G
> From: superboer7@t-online.de
> Subject: Re: Functional Indexes?
> Date: Wed, 9 Apr 2008 07:33:01 -0700
> To: informix-list@iiug.org
>
> Hello Mikey,
>
> Art was right; however i disagree on the rowid part. (fragmented
> tables!!! it's time that that is allowed/implemenented GRMBLL)
>
>
> anyways:
>
> create table mikey( a_id int, b_type char(1), c_seq serial, d_desc> char(100), primary key (c_seq) );
> -->> i assume you have a primary key on your table...... which is
> c_seq...
>
> create function mikeyfun ( a int, b char(1), c int)
> returning char(100)
> with
> (not variant)> ;
>
> if ( b = "C") THEN
> return a||b;
> else
> return a||b||c;
> end if;
> end function;
>
> create unique index mikeyix on mikey(mikeyfun(a_id, b_type,c_seq ));>
> insert into mikey values (1,"D",0,"myfirstone");
> insert into mikey values (1,"D",0,"mysecond one");>
> insert into mikey values (1,"C",0,"myfirstone");
> insert into mikey values (1,"C",0,"should fail");> # ^
> # 239: Could not insert new row - duplicate value in a UNIQUE INDEX
> column.
> #
> # 100: ISAM error: duplicate value for a record with unique key.
> #
>
> Assume the above is what you are after....
>
>
> And yes i did not like the fact that obstacle allowed for more then
> one sqlnull in a unique index...
> well if one knows then you can live with it and take countermeasures.
>
>
> Superboer.
>
> way fast=http://www.clipjes.nl/clip/nederlands/n/normaal_-
> _oerend_hard.html
>
>
>
> On 9 apr, 16:21, "Art S. Kagel (Oninit)" <a...@oninit.com> wrote:
> > Ian Michael Gumby wrote:
> >
> > Gumby:
> >
> > As one of those who, privately, said that the table design breaks the
> > relational model, I beg to differ. It does. Been writing normalized
> > relational model databases since a few weeks after Codd's article broke
> > (1980?). Damn I'm old! Coded them at first by hand, and later using a
> > long parade of relational databases starting with Grumman's RIM truly
> > the very first database engine to support the relational model in any
> > form. But enough rambling.
> >
> > It's not the index that causes the breakage, but the entire notion of
> > multiple row types in a single table using a 'type' field to
> > differentiate. The fact that one of the row types has a different set
> > of integrity constraints on it than the rest of the row types in the
> > table is simply one proof that the model is broken. The fact that for
> > the other row types the primary key is not unique is proof. If you want
> > to do this in a purely relational way then there should be a master
> > record table with several different child tables one for each content
> > type each related back to the master record which is unique. Each row
> > of each of the child tables should have a unique key as well some
> > identifier or sequence number. How else can you tell one type 'A'
> > record from another or order them correctly when you select them? With
> > that correctly done, the type field in the master record will point to
> > the correct child table and each child table can have its own
> > constraints and all is simple and easy.
> >
> > You can also do this using IDS's OO features so that fetching the master
> > record brings in the correct child record type. That's not relational
> > either, but it just fits a different model and the model is supported by
> > the engine.
> >
> > The way you have things now you are getting no schema support for your
> > model from the engine. Even Orable's ISAMish index kludge and the
> > functional index workaround that I sent to you directly are not really
> > model support, just work arounds for the models the engine is supposed
> > to be enforcing.
> >
> > Art S. Kagel
> > Oninit
> >
> >
> >
> > > > From: markbtowns...@sbcglobal.net
> > > > Subject: Re: Functional Indexes?
> > > > Date: Wed, 9 Apr 2008 05:52:42 +0000
> > > > To: informix-l...@iiug.org
> >
> > > > Ian Michael Gumby wrote:
> >
> > > > > The solution in Oracle is a neat little inline function. I was
> > > shocked.
> > > > > So what is the solution in DB2 and IDS? Postgres?
> >
> > > > Hmm - I'm guessing by returning nulls for the non C values so it's a
> > > > simple case statement or nvl call ?
> >
> > > > If so, the real question is - Does IDS allow nulls in a unique key ?
> >
> > > > (a little featurette in Oracle that suprisingly, some people don't like)
> >
> > > Yes, that's exactly it. A simple case statement which would return
> > > either the id, or NULL.
> > > You can't do that in Informix, and there wasn't a comment from the DB2
> > > peanut gallery.
> >
> > > While someone did point out that it "broke the relational model", it
> > > doesn't.
> > > This index would be a backing store index to guarantee uniqueness.
> > > Some could have said that the table organization wasn't a good model.
> > > While you could argue it, you could also claim that it was because
> > > you're creating a table that differentiates the type of varchar texts
> > > by type.
> >
> > > The interesting thing is the VII. It is theorectically possible to
> > > create your own index type that mimics the behavior that you find in
> > > Oracle. (I don't know VII, so I'll leave that open to a true VII guru)
> >
> > > So thanks to those who actually tried to answer the question. Of
> > > course for the individual who gave the "RFTM" here's the section on
> > > functional indexes, you didn't actually answer the question and well
> > > frankly, the "manual" didn't help.
> >
> > > The interesting exercise would be to write a VII to do exactly this.
> > > It actually has some unique uses and as long as you're not using it
> > > for a PK or FK, you're not going to break anything.
> >
> > > But hey! What do I know? When you support C/C++ Objective C, Python,
> > > Java, etc ... and a couple of relational databases, its difficult to
> > > keep things straight.
> >
> > > Oh and to be fai