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 ~
--
http://www.freelists.org/webpage/oracle-l
