If I had to do it, I would give the scientists a transaction table where
they entered their stuff. A trigger on this table would then update the
real "NAMES " table, marking the one prior record as "Previous " and inserting
the new record as "Current ".
It 's about the only way I can see this happening, as you cannot (within a
trigger) update other records within the same table.
Another option is to provide a PL/SQL package for the scientists to use.
They would call the package passing in the new information. The package
would then perform the correct updates to the NAMES table. This could work
with no new tables - just a change in operations.
Good Luck!
Tom Mercadante
Oracle Certified Professional
-- --Original Message-- --
From: Jared Still [mailto:jkstill@(protected)]
Sent: Wednesday, October 27, 2004 9:15 AM
To: wbfergus@(protected)
Cc: oracle-l@(protected)
Subject: Re: Check constraint
You might consider using a separate table for this.
Store the PK from the source table, and the column(s)
(name?) upon which the CURRENT uniqueness should
be employed, along with a unique constraint to enforce it.
It really sounds as if this data model could be optimized,
but not knowing the rest of the associated data, it 's difficult to say just
how it might be changed.
If this is a third party app, then you can probably disregard all of this.
Jared
On Wed, 27 Oct 2004 06:44:03 -0600, William B Ferguson <wbfergus@(protected) >
wrote:
> 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
>
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
