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 > > > I have a really cool solution in Oracle. > Now I'd like to see it in DB2 and IDS. > > So what's the syntax? > > -G > > > ------------------------------------------------------------------------ > 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>; > > ------------------------------------------------------------------------ > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >