Thanks for this. I like it. Never thought of that before.
Henry
-- --Original Message-- -- From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Lex de Haan Sent: Wednesday, December 07, 2005 3:31 AM To: oracle-l@(protected) Subject: RE: Function based indexes?
this just reminds me of an example I worked on a while ago with Diana Lorentz, for the SQL Reference, where two columns are involved: see http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements _501 0.htm#i2077034
and scroll down to this section:
Using a Function-based Index to Define Conditional Uniqueness: Example ====================================================================== The following statement creates a unique function-based index on the oe.orders table that prevents a customer from taking advantage of promotion ID 2 ("blowout sale") more than once:
CREATE UNIQUE INDEX promo_ix ON orders (CASE WHEN promotion_id = 2 THEN customer_id ELSE NULL END, CASE WHEN promotion_id = 2 THEN promotion_id ELSE NULL END);
