Re: Functional Indexes?

Ian Michael Gumby wrote: > > > > From: markbtownsend@sbcglobal.net > > Subject: Re: Functional Indexes? > > Date: Wed, 9 Apr 2008 05:52:42 +0000 > > To: informix-list@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. It does... If you have a parent table with a primary key which is null, how can you reference it from a child table? Anyway... Informix allows NULLs in components for composite primary keys. But in this case, I think the return value must be just one... Hmm... this is not very clear... A functional index is based on a function, and i think this function must return just one value... You can check > > 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. That will be me... You asked for the syntax... you have the syntax for the manual. You don't write the function inline... you write the function and reference it on the create index. The manuals explain this, if you let them bother you :) Regards, -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Fernando Nunes wrote: >> >> While someone did point out that it "broke the relational model", it >> doesn't. > > It does... If you have a parent table with a primary key which is null, > how can you reference it from a child table? I didn't say anything about nulls in primary keys. A unique index is different.

> From: markbtownsend@sbcglobal.net> Subject: Re: Functional Indexes?> Date: Thu, 10 Apr 2008 00:02:51 +0000> To: informix-list@iiug.org> > Fernando Nunes wrote:> > >> > >> While someone did point out that it "broke the relational model", it > >> doesn't.> > > > It does... If you have a parent table with a primary key which is null, > > how can you reference it from a child table?> > I didn't say anything about nulls in primary keys. A unique index is > different. Nor did I. You're creating what is called a "backing index" in order to ensure a type of uniqueness constraint. The relational model doesn't break period. Here's the solution in Oracle... CREATE TABLE test (prod_id NUMBER, type VARCHAR2(1), text VARCHAR2(20)); CREATE UNIQUE INDEX foo_idx ON test (CASE WHEN type ='P' THEN prod_id END); Note: in this test case we were using P and S (salt and pepper) as our TYPE values. Surely a guru of VII can write a comparable index so that it matches what Oracle does... -G _________________________________________________________________ More immediate than e-mail? Get instant access with Windows Live Messenger. http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_instantaccess_042008

Mark Townsend wrote: > Fernando Nunes wrote: > >>> >>> While someone did point out that it "broke the relational model", it >>> doesn't. >> >> It does... If you have a parent table with a primary key which is >> null, how can you reference it from a child table? > > I didn't say anything about nulls in primary keys. A unique index is > different. I don't think I said you did... But for me, it doesn't make too much sense to have uniqueness with duplication... But anyway... I regularly have to explain to Oracle minded developers why "" is != NULL, and why "C"||NULL != "C" and why ("C" != NULL) is false (maybe not this last one) There are bad things that can be handy sometimes... I don't say the opposite. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...