From oracle-l-bounce@freelists.org Wed Oct 27 09:28:31 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i9RESVg01979 for ; Wed, 27 Oct 2004 09:28:31 -0500 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 i9RESSI01963 for ; Wed, 27 Oct 2004 09:28:28 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 86E3872CCB0; Wed, 27 Oct 2004 09:34:21 -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 14818-55; Wed, 27 Oct 2004 09:34:21 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 13BE572EE87; Wed, 27 Oct 2004 09:32:56 -0500 (EST) Message-ID: From: Ken Naim To: "'wbfergus@usgs.gov'" , oracle-l@freelists.org Subject: RE: Check constraint Date: Wed, 27 Oct 2004 10:31:04 -0400 MIME-Version: 1.0 Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 11581 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: ken@clubmom.com Precedence: normal Reply-To: ken@clubmom.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org You can create a composite unique index on decode (status,'Current',dep_id,null), decode (status,'Current',line,null), decode (status,'Current',status,null) which will reject any duplicate values when the status is current. Indexes do not contain values for all null entries. -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of William B Ferguson Sent: Wednesday, October 27, 2004 8:44 AM To: oracle-l@freelists.org Subject: Check constraint This has probably been hashed numerous times already, but I can't find = it. I have a table (names) with the following structure: dep_id number(12) -- FK to main table line number(4) -- PK when combined with dep_id name varchar2(70) varchar2(8) - can be either Current or Previous ...and other auditing fields. My question is, I need/want to have a check constraint (somehow), so = that for each dep_id, there is only one record with the status of 'Current'. There can (and are) many previous names, but there should only be one = name flagged as 'Current'. I was thinking about an unique index, but then discarded that, as each row is unique with dep_id||line, but I want duplicates of dep_id|| and only one dep_id||. I also thought about forcing the user/submitter to always make line=3D1 the 'Current', but that would = never work, they're 'scientists" and therefore are unrestrained by rules. I = then thought about adding a column for a sequence, but even with multiple current's for the same dep_id it would still be unique. Using the name doesn't always work either, as many sites in different areas use the = same name, like 'El Dorado Mine'. How can I enforce this at the table level, since right now we are still getting data updates from spreadsheets and other data sources? The = asktom site (http://asktom.oracle.com/pls/ask/f?p=3D4950:8:::::F4950_P8_DISPLAYID:124= 980 0833250) almost has a way using a function based index, but I can't see how that approach works in my situation. Thanks ------------------------------------------------------------ Bill Ferguson U.S. Geological Survey - Minerals Information Team PO Box 25046, MS-750 Denver, Colorado 80225 Voice (303)236-8747 ext. 321 Fax (303)236-4208 ~ Think on a grand scale, start to implement on a small scale ~ -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l