From oracle-l-bounce@freelists.org Fri Dec 3 14:56:05 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id iB3Ku5920608 for ; Fri, 3 Dec 2004 14:56:05 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id iB3Ku5d20603 for ; Fri, 3 Dec 2004 14:56:05 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8B50472C7ED; Fri, 3 Dec 2004 16:02:28 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 00873-42; Fri, 3 Dec 2004 16:02:28 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2829972C968; Fri, 3 Dec 2004 16:01:31 -0500 (EST) Message-ID: <41B0D372.6040803@alumni.sfu.ca> Date: Fri, 03 Dec 2004 12:58:26 -0800 From: Lyndon Tiu User-Agent: Mozilla Thunderbird 0.9 (Windows/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Justis Durkee Cc: oracle-l@freelists.org Subject: Re: Check Contraint Question References: <41B0C29F.3050402@alumni.sfu.ca> <41B0C921.4010702@mnl.com> In-Reply-To: <41B0C921.4010702@mnl.com> Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-OriginalArrivalTime: 03 Dec 2004 20:58:26.0490 (UTC) FILETIME=[D587C9A0:01C4D97A] X-archive-position: 13113 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: ltiu@alumni.sfu.ca Precedence: normal Reply-To: ltiu@alumni.sfu.ca X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Justis Durkee wrote: > One way to accomplish this requirement is to create a unique index on > column1 and column2, so column1 will only be indexed if column 2 = 1. > > SQL> create table t( > 2 column1 varchar2(32), > 3 column2 number constraint check_column2 check( column2 in (0,1) ) > 4 ); > > Table created. > > SQL> create unique index t_unique on t( decode( column2, 1, column1, > null ) ); This does it, thank you very much. -- Lyndon Tiu -- http://www.freelists.org/webpage/oracle-l