RE: Functional Indexes?

> Date: Tue, 8 Apr 2008 12:29:19 -0400 > From: art@oninit.com > To: im_gumby@hotmail.com > CC: informix-list@iiug.org > Subject: Re: Functional Indexes? > > Ian Michael Gumby wrote: > > Ok sports fans, here's a really fun problem. > > > > Suppose you have a table with the following columns: > > > > Table Foo: > > Column a: id (Integer) > > Column b: type (single character) > > Column c: sequence # > > Column d: description (varchar up to 128 characters wide) > > > > Now there are 3 types of records: "A", "B","C" > > > > Here's where the fun begins. > > > > I need to ensure that if I have a record of type "C", the pair (id, > > type) must be unique. > > For the other record types, I need to allow for multiple entries per id. > > > > So how do you do this? > > > > The answer is create a functional index that ensures uniqueness for > > records of type "C" per id. > > I don't think that a unique index would work, hmm, except perhaps by > appending additional chars for types A & B that would force the key to > be unique. > > You could do it with INSERT and UPDATE triggers calling a UDR. That > would be minimally invasive for types A & B as there's no check to > perform. Only for type C records would it have to look for a dup. > > Art S. Kagel > Oninit You have to think beyond a simple index. In Oracle, you have the ability to create a functional index a simple one is to create an index on UPPER(last_name) which will create an index of the last name in all upper case. The idea is that you create a functional index that is dependent on type = C and the ID combined being unique. If the type doesn't = 'C', its not going to be added to the functional index. You don't want to do insert triggers or a trigger at all because of the overhead. Again the idea is to have a portable method that could be used in different databases. _________________________________________________________________ Get in touch in an instant. Get Windows Live Messenger now. http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_getintouch_042008