Check constraint
- From: "William B Ferguson" <wbfergus@xxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 27 Oct 2004 06:44:03 -0600
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)
status 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 ~
--
//www.freelists.org/webpage/oracle-l
Other related posts:
