Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Check constraint

RE: Check constraint

From: Ken Naim <ken_at_clubmom.com>
Date: Wed, 27 Oct 2004 10:31:04 -0400
Message-ID: <EA052B73AC607549BCB8BFEF4747DA210214@cmiechange>


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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of William B Ferguson
Sent: Wednesday, October 27, 2004 8:44 AM To: oracle-l_at_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||<status=3D'Previous'> and only one dep_id||<status=3D'Current'>. 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
Received on Wed Oct 27 2004 - 09:28:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US